August 19, 2018 at 1:27 pm
The table I'm query against is such:
ID Dateworked Time(seconds)
AAA 2007-10-17 00:00:00.000 21600
AAA 2007-10-17 00:00:00.000 4020
AAA 2007-10-22 00:00:00.000 25200
BBB 2016-08-19 00:00:00.000 3600
BBB 2016-08-22 00:00:00.000 3600
BBB 2016-08-22 00:00:00.000 1800
CCC 2002-02-06 00:00:00.000 360
CCC 2002-02-06 00:00:00.000 360
CCC 2002-02-06 00:00:00.000 360
CCC 2002-02-06 00:00:00.000 360
I'm trying to produce one row which list only there name and total time, I ve tried select Id, convert(verchar(12),sum(time)/60 %60) from tableA Group by rollup(ID, time) but I get the Grand total for each new ID. What am I missing.
August 19, 2018 at 1:47 pm
That query you given won't work as it is, as one it contains at least one syntax error.
What is the actual results you want here? I think, if I understand your goal correctly, all you need is to omit the ROLLUP; after you fix the syntax errors.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 19, 2018 at 1:58 pm
Select
id,
sum(time)
From #Temp
group by id
Select
id,
sum(time)
From #Temp
group by rollup(id)
August 20, 2018 at 8:00 am
ROLLUP will always produce a grand total. If you don't want a grand total, you should be using GROUPING SETS instead, because it allows you to specify exactly which totals you are interested in.
GROUP BY ROLLUP(id, time) is simply shorthand for GROUP BY GROUPING SETS((), (id), (id, time)). I think you want GROUP BY GROUPING SETS((id), (id,time)). NOTE the missing empty set.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply