July 13, 2014 at 8:25 am
Hi all,
I have a table that contains multiple activities by empid and I need to rollup counts on each activity.
Basically have 1 row per empid with counts of each item as new columns.
I'm struggling with the below...
DECLARE @RowCount INT
DECLARE @I INT
DECLARE @OPENS INT
DECLARE @CLICKS INT
SET @RowCount = (SELECT COUNT(employeeid) FROM tablename)
SET @I = 1
SET @OPENS = 0
SET @CLICKS = 0
select distinct
employeeid,
historyid,
WHILE (@I <= @RowCount)
BEGIN
if activitytype = 'OPEN' then OPENS + 1 end
if activitytype = 'CLICK' then CLICKS + 1 end
SET @I = @I + 1
end
from tablename
group by employeeid,historyid
Output should look like:
EmployeeID HistoryId Opens Clicks
123 8989 3 2
124 8989 0 1
Help please....thanks!
July 13, 2014 at 9:45 am
I figured it out...never mind! 🙂
July 13, 2014 at 11:29 am
Well, I am glad you solved your problem. Etiquette, however, says that you should post your solution to your problem as it may help others with a similar problem.
July 14, 2014 at 8:01 am
One solution is to sum on a conditional expression like so:
select employeeid, historyid,
sum(case when activitytype = 'OPEN' then 1 else 0 end) Opens,
sum(case when activitytype = 'CLICK' then 1 else 0 end) Clicks
from tablename
group by employeeid, historyid;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply