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
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Aggregate Functions

Author  Topic 

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-11 : 10:33:04
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

28 Posts

Posted - 2013-04-11 : 11:14:47
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-11 : 11:55:16
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-11 : 11:55:16
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

28 Posts

Posted - 2013-04-11 : 15:44:42
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-11 : 16:26:45
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
Go to Top of Page

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-12 : 03:20:22
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 03:25:45
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

28 Posts

Posted - 2013-04-12 : 03:32:44
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

28 Posts

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 03:38:55
[code]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[/code]
Go to Top of Page

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-12 : 04:36:41
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 04:42:10
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
   

- Advertisement -