Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count of Table values

Author  Topic 

shinelawrence
Starting Member

32 Posts

Posted - 2013-02-04 : 07:06:36
Hi Everyone,
I want to take the count of table values.
Eg:
My Table:
Id Name Month Date1 Date2 Date3 Date4 Date5
1 Lawrence January Present Am Pm Absent Present
2 Prem January Present Present Present Present Present

I need to like this result for month wise :

ID Name Present Am Pm Absent
1 Lawrence 2 1 1 1
2 Prem 5 0 0 0

How to do..Please tell the solution...

Thanks In Advanced

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 07:13:05
[code]
SELECT Id,Name,[Month],
SUM(CASE WHEN Attendance = 'Present' THEN 1 ELSE 0 END) AS Present,
SUM(CASE WHEN Attendance = 'Am' THEN 1 ELSE 0 END) AS AM,
SUM(CASE WHEN Attendance = 'Pm' THEN 1 ELSE 0 END) AS PM,
SUM(CASE WHEN Attendance = 'Absent' THEN 1 ELSE 0 END) AS Absent
FROM
(
SELECT Id,Name,[Month],DateVal,Attendance
FROM table t
UNPIVOT (Attendance FOR DateVal IN ([Date1],[Date2],[Date3],[Date4],[Date5]))u
)m
GROUP BY Id,Name,[Month]
[/code]


if you cant determine dates before hand use below logic to generate it dynamically

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shinelawrence
Starting Member

32 Posts

Posted - 2013-02-05 : 00:32:57
thank you for your kindly reply....

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 00:34:17
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -