November 16, 2005 at 1:35 pm
I have a query that produces the number of visits and payments by month for a year and cannot figure out how to total the columns. I use Enterprise Guide to produce the code. I am allowed to type additional code into the code window prior to running the query.
November 16, 2005 at 2:45 pm
Im guessing you want something like this:
Month Visits payments
Jan 5 8
Feb 8 7
Mar 10 2
Total 23 17
if you are using a query like this:
select [month],sum(visits) as visits,sum(payments) as payments group by [month]
you would want to do this:
select
select [month],sum(visits) as visits,sum(payments) as payments group by [month] UNION select 'Total',sum(visits),sum(payments)
Notice all I did was add a union with the same query underneath, but instead of a group by, I just return 'Total' Note:use the same where clause on both queries
November 17, 2005 at 12:19 am
Use UNION ALL rather than UNION. There's no possibility of duplicates between the two queries.
UNION means SQL will take the two result sets, concatinate, then sort to remove duplicates. UNION ALL omits the last step.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2005 at 8:16 am
I think you should all look into the use of ROLLUP in the GROUP BY clause and GROUPING in the column list.
Try
select
Case GROUPING([month])
WHEN 0 THEN [month]
WHEN 1 THEN 'Total'
END as Month
, sum(visits) as visits
,sum(payments) as payments
from monthVisitPayments
group by rollup([month])
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply