question

jacobmat9 avatar image
jacobmat9 asked

sum and count with group by in a single column

I want to SUM from the result set of count(AdjustedDateCompleted)for each day. select COUNT(AdjustedDateCompleted) as BRRCompletedApr13 from dbo.shp_BRRPipelineRemediation where AdjustedDateCompleted >='04/01/2013' and AdjustedDateCompleted <'05/01/2013' GROUP BY AdjustedDateCompleted Order By AdjustedDateCompleted I tried different ways to SUM it. but failed. Please help.
aggregates
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you both for responding. I got a syntax error after the group by in the query SirSql send. The one mjharper send works, but i want the (AdjustedDateCompleted)as BRRCompletedApr13 total for each day for a month and also show the grant total. for eg. **BRRCompletedApr13** Apr01 -25 Apr02 -10 Grant Total =35 If possible only BRRCompletedApr13 column in the resultset is needed. No need to show Apr01 or Apr02, just 25, 10 etc with a grant total = xxxxx My new query which shows the total for a day is - select sum([BRR Completed]) from ( select COUNT(AdjustedDateCompleted)as [BRR Completed] , BRRStatus from dbo.shp_BRRPipelineRemediation where AdjustedDateCompleted >='04/01/2013' and AdjustedDateCompleted <='04/02/2013' Group by BRRStatus) as p so i want to show all 30 days of apr with the total for each day and grand total at the end.
0 Likes 0 ·
If any of these answers was helpful, please indicate that by clicking on the thumbs up next to those answers. If any of the answers solved the problem, please indicate that by clicking on the check box next to that answer.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
A little confused here, why not just use SUM instead of COUNT and eliminate your GROUP BY? Or make your existing select a group by SELECT SUM(BRRCompletedApr13) from (select COUNT(AdjustedDateCompleted) as BRRCompletedApr13 from dbo.shp_BRRPipelineRemediation where AdjustedDateCompleted >='04/01/2013' and AdjustedDateCompleted
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image
mjharper answered
I think you just need to include AdjustedDateCompleted in your SELECT and COUNT the rows. So this: select AdjustedDateCompleted, COUNT(*) as BRRCompletedApr13 from dbo.shp_BRRPipelineRemediation where AdjustedDateCompleted >='04/01/2013' and AdjustedDateCompleted
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Getting error Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'UNION'.
0 Likes 0 ·
Sorry...the syntax error is due to the ORDER BY before the UNION ALL. Remove that and it should work.
0 Likes 0 ·
mjharper avatar image
mjharper answered
The syntax error will be because AdjustedDateCompleted can't be summed as I guess it's a datetime field. To get both day by day and total I would do something like this: select AdjustedDateCompleted, COUNT(*) as BRRCompletedApr13 from dbo.shp_BRRPipelineRemediation where AdjustedDateCompleted >='04/01/2013' and AdjustedDateCompleted ='04/01/2013' and AdjustedDateCompleted
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tried that and gives me the results, but isn't there a easier way to do it rather than doing UNION ALL for 30 times to get the total for each day for a month.. and how will i get the grand total ?
0 Likes 0 ·
Doesn't the first SELECT give you one record for each day and the second SELECT give you the grand total?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.