RE: SQL : subtotals and total

The answer is no.

SQL doesn't specifically support totals/subtotals of the data in a results set.

SQL <> Reporting Tool

Instead, such functionality is left to whatever asked for the data in the first place. For example,
Query Manager. You create a QM query to get the data and a QM form to format the results. One thing
you can do with the form is tell it where you want level breaks and totals.

Having said all that, once and a while it's necessary to simulate such functionality (but note there
is a performance price to pay).

You can do so with a UNION ALL of the two (or more) queries.

Select ID, Product, Level, sum(number) as total, '01' as rptLevel
From mytable
Group by ID, Product, Level

UNION ALL

Select ID, Product, ' ' as level, sum(number) as total, '02' as rptLevel
From mytable
Group by ID, Product

ORDER BY ID, Product, rptLevel, level



Note the ORDER BY applies to the entire UNION'd result set. Also, when using UNION, each query must
return the same number and types of columns. Thus, the need for a ' ' as level in the second.

More examples in the archives.

HTH,


Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Wednesday, August 06, 2008 5:03 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL : subtotals and total

Hi,

I have the feeling this has been answered already but I can' t find it.

I have to produce the following :

ID Product Level Total


I've created a view that does select sum (number) as total with group by
ID, product, level, etc.

This gives a total for each level of a product.

I also have to add the total of all the levels so I've created a view over
my first view to do this.

My final table would look like this :

ID Product Level Total
1 A 1 2
1 A 2 6
1 A 8

To get there I'm creating the first view, then a second on that view, then
doing 2 inserts based on those views.
Its all looking very messy.


I think there is a way to produce subtotals and an overall total with the
OVER clause.

Does anyone know if this is possible to do?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.