[thelist] Stored Procedures
rudy
r937 at interlog.com
Thu Jan 30 07:02:02 CST 2003
> Thinking about it - maybe it's the WHERE section of the total section
> that is messing it up a little...
no, you want the detail and totals queries to use exactly the same WHERE
conditions, i.e. return results from the same set of rows
since your "detail" query was already doing aggregates, it's easy to see why
you got confused in writing the "total" query
the "detail" query gets one row for each date, even though the values for
each date are sums
the "totals" query should get one row for all dates, therefore just remove
the GROUP BY, and let the same SUM() expressions operate over all rows
select "detail"
, CONVERT(varchar(10), g.DateCreated, 105) AS Date
, Sum(CAST(i.OtherEnq as int)) as 'Other Enquiry',
...
from ...
where ...
group
by CONVERT(varchar(10), g.DateCreated, 105)
union all
select "total"
, "all"
, Sum(CAST(i.OtherEnq as int)) as 'Other Enquiry',
...
from ...
where ...
order
by 1, 2
rudy
More information about the thelist
mailing list