SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count of Table values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shinelawrence
Starting Member

India
32 Posts

Posted - 02/04/2013 :  07:06:36  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/04/2013 :  07:13:05  Show Profile  Reply with Quote

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]



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/


Edited by - visakh16 on 02/04/2013 07:14:41
Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 02/05/2013 :  00:32:57  Show Profile  Reply with Quote
thank you for your kindly reply....

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/05/2013 :  00:34:17  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000