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
 Looping and Counts

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 INT
DECLARE @I INT
DECLARE @APPLES INT
DECLARE @ORANGES INT
SET @RowCount = (SELECT COUNT(employeeid) FROM tablename)
SET @I = 1
SET @APPLES = 0
SET @ORANGES = 0

select distinct
employeeid,
WHILE (@I <= @RowCount)
BEGIN
if emailactivitytype = 'APPLE' then APPLES + 1 end
if emailactivitytype = 'ORANGE' then ORANGES + 1 end
SET @I = @I + 1
end
from tablename
group by employeeid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-13 : 09:24:56
[code]SELECT employeeid, emailactivitytype, count(*)
FROM tablename
GROUP BY employeeid, emailactivitytype[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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.
Go to Top of Page

caoneill
Starting Member

10 Posts

Posted - 2014-07-13 : 09:37:00
Example:

EmployeeID Apples Oranges
123 3 2
124 1 5

I'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?
Go to Top of Page

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.
Go to Top of Page

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 18
123 ORANGE 53

I need 1 row per empid with counts of each type as new column.

Emplid Apple Orange
123 18 53

Thanks for your help!
Go to Top of Page

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 Banana
FROM dbo.TableName
GROUP BY EmployeeID;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

caoneill
Starting Member

10 Posts

Posted - 2014-07-13 : 11:37:13
Perfect...thanks SwePeso!
Go to Top of Page

caoneill
Starting Member

10 Posts

Posted - 2014-07-13 : 12:01:36
Spoke to soon...
it's still giving me multiple rows per empid
Empid Apple Banana
123 0 10
123 5 2
Go to Top of Page

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....
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -