Author |
Topic |
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 09:20:05
|
Hi all,I am trying to loop through a table and count the number of occurrences of items within a column for each id.I know the syntax is off...help please.Thanks!DECLARE @RowCount INTDECLARE @I INTDECLARE @APPLES INTDECLARE @ORANGES INTSET @RowCount = (SELECT COUNT(employeeid) FROM tablename)SET @I = 1SET @APPLES = 0SET @ORANGES = 0select distinctemployeeid,WHILE (@I <= @RowCount)BEGIN if emailactivitytype = 'APPLE' then APPLES + 1 end if emailactivitytype = 'ORANGE' then ORANGES + 1 end SET @I = @I + 1endfrom tablenamegroup by employeeid |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-07-13 : 09:24:56
|
[code]SELECT employeeid, emailactivitytype, count(*)FROM tablenameGROUP BY employeeid, emailactivitytype[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 09:29:24
|
Thanks for the quick response!I need the counts for each activity by employeeid not an overall count though. |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 09:37:00
|
Example:EmployeeID Apples Oranges123 3 2124 1 5I'm trying to take a table with multiple rows per employee id and roll up the counts for each type into a new column.Make sense? |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-07-13 : 11:13:23
|
khtan's solution will do exactly what you described - just try it. |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 11:25:53
|
Hi bitsmed,So khtan's solution gives me multiple rows per empid.123 APPLE 18123 ORANGE 53I need 1 row per empid with counts of each type as new column.Emplid Apple Orange123 18 53Thanks for your help! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-13 : 11:29:01
|
[code]SELECT EmployeeID, SUM(CASE WHEN EmailActivityType = 'Apple' THEN 1 ELSE 0 END) AS Apple, SUM(CASE WHEN EmailActivityType = 'Banana' THEN 1 ELSE 0 END) AS BananaFROM dbo.TableNameGROUP BY EmployeeID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 11:37:13
|
Perfect...thanks SwePeso! |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 12:01:36
|
Spoke to soon...it's still giving me multiple rows per empidEmpid Apple Banana123 0 10123 5 2 |
|
|
caoneill
Starting Member
10 Posts |
Posted - 2014-07-13 : 12:07:21
|
Never mind! I had added a date to group by and it was throwing thing off.... |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-07-13 : 12:08:43
|
Sorry for my mistake.Alternative you could use pivot:select employeeid ,Orange as Apples ,Apple as Oranges ,Banana as Bananas from yourtable pivot (count(emailactivitytype) for emailactivitytype in ([Apple],[Orange],[Banana]) ) as pv |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-13 : 12:08:53
|
Show us your code. It is not possible to get multiple rows over EmployeeID with my suggestion. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-13 : 12:08:53
|
Show us your code. It is not possible to get multiple rows over EmployeeID with my suggestion. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|