pivot tables - total column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

pivot tables - total column

Author

Topic

lightningtomo
Starting Member

4 Posts

Posted - 2002-05-20 : 06:07:44

Am using pivot table (below) to retrieve the number of voicemail messages received per hour every day. I need to tack on a 'total' column at the end for each day. Sounds really simple but I can't do it and doing it in client program is not an option. Anyone able to help? None of the examples I've read seem to address this - please point me to one if I'm wrong.

-- Code so far!
SELECT CONVERT(varchar(10), timestamp,103) AS Day,
SUM(CASE WHEN DATEPART(hour,[timestamp]) = 0 THEN 1 ELSE 0 END) AS '00:00'
.
.
.
GROUP BY CONVERT(varchar(10),timestamp,103)
ORDER BY CONVERT(varchar(10),timestamp,103)
--

Thanks

Ben

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 09:23:11

Just add a SUM() to the end of the SELECT list:

SELECT CONVERT(varchar(10), timestamp,103) AS Day,
SUM(CASE WHEN DATEPART(hour,[timestamp]) = 0 THEN 1 ELSE 0 END) AS '00:00'
.
.
.,
SUM(1) AS Total
GROUP BY CONVERT(varchar(10),timestamp,103)
ORDER BY CONVERT(varchar(10),timestamp,103)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources