problem on a gridview to display count.. help me out

Posted by Suneel161 under Regular Expressions on 4/21/2010 | Views : 2344 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i have taken a fields like status,month(status),count(status).IF i select then the totalcount and related month should be displayed depending upon the status.
i want it to displayed in gridview.how to write the query.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pavanandey on: 4/21/2010 [Member] Bronze

0	Hi Consider the below table with the following structure and data CREATE TABLE [dbo].[TEST]( [id] [int] IDENTITY(1,1) NOT NULL, [date] [datetime] NULL, [status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] id date status ------------------------------------------------- 1 01/01/2010 00:00:00 y 2 08/01/2010 00:00:00 n 3 13/02/2010 00:00:00 y 4 14/02/2010 00:00:00 n 5 15/02/2010 00:00:00 n 6 02/03/2010 00:00:00 n 7 05/03/2010 00:00:00 n select datename(mm,date) as Month,count(status) as StatusCount,status from test group by datename(mm,date),status Month StatusCount status ------------------------------------------ February 2 n January 1 n March 2 n February 1 y January 1 y Hope ur requiremnt is satsified by this Thanks Pavan Kumar Mark Answer if this fits the need Suneel161, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Consider the below table with the following structure and data

CREATE TABLE [dbo].[TEST](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [datetime] NULL,
[status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

id date status
-------------------------------------------------
1 01/01/2010 00:00:00 y
2 08/01/2010 00:00:00 n
3 13/02/2010 00:00:00 y
4 14/02/2010 00:00:00 n
5 15/02/2010 00:00:00 n
6 02/03/2010 00:00:00 n
7 05/03/2010 00:00:00 n

select datename(mm,date) as Month,count(status) as StatusCount,status from test group by datename(mm,date),status

Month StatusCount status
------------------------------------------
February 2 n
January 1 n
March 2 n
February 1 y
January 1 y

Hope ur requiremnt is satsified by this

Thanks
Pavan Kumar
Mark Answer if this fits the need

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts