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
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Aggregate Functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/11/2013 :  10:33:04  Show Profile  Reply with Quote
I want to count tasks complete for staff members and list them by task, so the following;

SELECT Task, count(*)
FROM Workload
WHERE Staff_member = "Joe Bloggs"
GROUP BY Task

How can I repeat this in a second column, with each column looking at only a specific month in my Date column (which contains months).

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/11/2013 :  11:14:47  Show Profile  Reply with Quote
To paraphrase, I have the following:

SELECT Task, Count(Task) as March
FROM Completed
WHERE datepart('m',Required_Date) = 3
GROUP BY Task_Main_Cat

Gives me a column with the names of the Tasks and a column with the count of those Tasks required in March.

How do I put another column next to this with the same count but for April?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/11/2013 :  11:55:16  Show Profile  Reply with Quote
SELECT Task,DateName(month,getdate()) as [Month], Count(Task)
FROM Completed
GROUP BY Task,DateName(month,getdate())

Cheers
MIK
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/11/2013 :  11:55:16  Show Profile  Reply with Quote
SELECT Task,DateName(month,getdate()) as [Month], Count(Task)
FROM Completed
GROUP BY Task,DateName(month,getdate())

Cheers
MIK
Go to Top of Page

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/11/2013 :  15:44:42  Show Profile  Reply with Quote
Thanks but that's not quite what I was looking for.

I want in column one a list of all the tasks, in column 2 the number of times those tasks were completed in month1, and in column 3 the number of times they were completed in month2. So the headers for the columns would read Task, February, March (for example) and the second and third columns contain counts of the tasks.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/11/2013 :  16:26:45  Show Profile  Reply with Quote
It sounds like you want a Pivot (http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx). But it's hard to tell without sample data so, plesae post sample data and expected output in a consumable format. Here are some links to show you how to perpare your question so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - Lamprey on 04/11/2013 16:44:36
Go to Top of Page

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/12/2013 :  03:20:22  Show Profile  Reply with Quote
I've had a good look over those two links and I can't see any instruction as to how I insert a table into a forum post.. I can do a little html but I don't want to try inserting it straight in here only for it not to interpret. I want to be able to show you the table of data I'm starting with and then what I'd like to end up with.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  03:25:45  Show Profile  Reply with Quote
Generate script of that table from SSMS --> copy & Paste in the forum post

Right-click on Database --> Generate Scripts --> Select only specific objects --> Tables (select only the specific table names) --> choose Schema and data under options
Go to Top of Page

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/12/2013 :  03:32:44  Show Profile  Reply with Quote
I start with the following table:

task date
-------------------------
Analysis January
Analysis February
Report January
Report January

And I want to produce the following table:

Task January February
-----------------------
Analysis 1 1
Report 2 0
Go to Top of Page

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/12/2013 :  03:33:53  Show Profile  Reply with Quote
I'm not sure how to use SSMS, I'm linking tables from one excel file to another.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  03:38:55  Show Profile  Reply with Quote
SELECT task
	,COUNT(CASE WHEN date = 'January' then task END)  JanCount
	,COUNT(CASE WHEN date = 'February' then task END)  FebCount
FROM TableName
GROUP BY task
Go to Top of Page

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 04/12/2013 :  04:36:41  Show Profile  Reply with Quote
Thanks but I get the following error:

Incorrect column expression: 'COUNT(CASE WHEN datepart('m', date) = 1 then task END)'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  04:42:10  Show Profile  Reply with Quote
quote:
Originally posted by stoolpidgeon

Thanks but I get the following error:

Incorrect column expression: 'COUNT(CASE WHEN datepart('m', date) = 1 then task END)'


It should be
COUNT(CASE WHEN datepart(m, date) = 1 then task END) AS 'January'

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000