Horizontal and vertical totals

[OldSlowDog]

Hello,
Here is the query I am runnin in SQL 2000.

SELECT convert(char(10), CloseDate, 101) as Date,
Cinema1 = SUM(CASE WHEN Loc =1 THEN TicketSales END),
Cinema2 = SUM(CASE WHEN Loc =2 THEN TicketSales END),
Cinema3 = SUM(CASE WHEN Loc =3 THEN TicketSales END)
FROM TicketSales_tbl
Where CloseDate > '3/1/2008' and CloseDate < '4/1/2008'
GROUP BY CloseDate
ORDER BY CloseDate

The result is:
Date Cinema1 Cinema2 Cinema3
3/1/2008 27 39 41
3/2/2008 24 31 33
3/3/2008 18 22 27

What I like to have are the vertical and horizontal totals:
Date Cinema1 Cinema2 Cinema3 Daily
3/1/2008 27 39 41 107
3/2/2008 24 31 33 88
3/3/2008 18 22 27 67
Monthly 69 92 101 262

Would you show me how to go about getting the totals?
And, if I want to have the results stored in a table do I place an INSERT at the beginning of the sql statement?

Thank you for helping in advance.

 

[bborissov]

Try:

[COLOR=green]-- That is for testing purposes only
[/color][COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (CloseDate [COLOR=#FF00FF]datetime[/color], Loc [COLOR=blue]int[/color], TicketSales [COLOR=blue]numeric[/color](12,2))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 1, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 2, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 3, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 1, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 2, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 3, 123)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080301'[/color], 1, 123)

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'20080302'[/color], 1, 123)
[COLOR=green]-- END
[/color]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](10), CloseDate,  101) [COLOR=blue]as[/color] [COLOR=blue]Date[/color],
    Cinema1 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =1 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Cinema2 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =2 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Cinema3 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =3 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Daily   = [COLOR=#FF00FF]ISNULL[/color](SUM(TicketSales),0)
[COLOR=blue]FROM[/color] @test TicketSales_tbl [COLOR=green]-- Remove reference to @Test
[/color][COLOR=blue]Where[/color] CloseDate >= [COLOR=red]'20080301'[/color] and CloseDate < [COLOR=red]'20080401'[/color]
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] CloseDate
UNION ALL
[COLOR=blue]SELECT[/color] [COLOR=red]'Monthly'[/color] [COLOR=blue]as[/color] [COLOR=blue]Date[/color],
    Cinema1 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =1 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Cinema2 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =2 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Cinema3 = [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Loc =3 [COLOR=blue]THEN[/color] TicketSales [COLOR=blue]END[/color]),0),
    Daily   = [COLOR=#FF00FF]ISNULL[/color](SUM(TicketSales),0)
[COLOR=blue]FROM[/color] @test TicketSales_tbl[COLOR=green]-- Remove reference to @Test
[/color][COLOR=blue]Where[/color] CloseDate >= [COLOR=red]'20080301'[/color] and CloseDate < [COLOR=red]'20080401'[/color]
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] 1

Just remove table variable declaration and

But why you need this?
That could easily done with your representation layer. No need to transfer all these over the network.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[OldSlowDog]

Thank you for helping bborissov !
In reality I have about 45 cinemas to show. I am thinking to show 10 cinemas at a time. The daily total will be shown at the last screen.

Is there a better way to do this. I am using Access as the front end and sql 2000 is the back end. Any suggestions are welcome.

Thanks again.

 

[bborissov]

As I said put these totals in Access report.
I'm didn't use Access but i know that you could add aggregate field in your report that will handle totals, one in the summary band (or Group band if you have one) and one in detal bad. The daily total colun will look like this:
Cinema1+Cinema2...+CinemaN

The Montly total (in the Summary or Group band)
=SUM(Cinema1) =SUM(Cinema2) ... ==SUM(Cinema1+Cinema2...)
(use the appropriate syntax for Access here)


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

Ask the question in the Access forum and they'll show you how to create totals in your forms/reports. That's a much better solution than doing it in the back end.

For what it's worth, a WITH ROLLUP clause and some HAVING GROUPING(ColumnName) expressions would get you the "vertical total" without using UNION.