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

Posted by Suneel161 under Sql Server on 4/21/2010 | Views : 1209 | Status : [Member] | Replies : 1
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.




Responses

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

Up
0
Down
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

Login to post response