Union Query with sums in two places

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

Transact-SQL (2000)

Union Query with sums in two places

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-16 : 08:51:06

Daniel Fountain writes "Hey,

I am reasonably new to SQL and i cant find the answer to what i am looking to do - probably because i dont know what i am looking for.

Below is an example of what i want to do - its not actually what i am doing but its just to show you.

If i have 2 identical tables which i query and join together as a union i would get the below

REF Product ProductCat Price
------------------------------------------------------
1 Lawnmower A 200
2 Hedgetrimmer A 150
3 Strimmer A 250
4 Shirt B 15
5 Trousers B 20
6 Boxers B 10

Ok well it adds the information from tableA with product CAT A to whats again in table A with productcat B.

Now you maybe asking why i want to do this, well the result i am actually looking for is this

REF Product ProductCat Price
------------------------------------------------------
1 Lawnmower A 200
2 Hedgetrimmer A 150
3 Strimmer A 250
------------------------------------------------------
Sub Calc Row 3 200
------------------------------------------------------
4 Shirt B 15
5 Trousers B 20
6 Boxers B 10
------------------------------------------------------
Sub Calc Row 3 15
------------------------------------------------------
Total calc row 6 107.5
------------------------------------------------------

Please dont worry about the reason why i am doing this, because the example i have gave isnt the real life scenario - just something i made up.

What it is doing is counting the prodCAT then Averaging the price for the CAT for each query. Then there is an overall calc row which is the total of both the productCAT sums and the average of both the average price. I hope this makes sense.

I know how to do the sum row (union the sum to the bottom of the row but the first problem is that all the rows have to be the same layout, so am having to do meaningless sums (counting everything). Is there (if i cant do anything else) a way to make the sums blank.

Then how do i make blank lines to break it all up?? (or dotted lines as above)

Lastly how would i do the total sum?

Thanks in advance.

Dan"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 13:32:19

well maybe compute by will help you in some way...

select *
from MyTable
order by ProductCat, Price
compute avg(Price) by ProductCat

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-16 : 13:42:51

Every question you have asked has to do with formatting output -- this is a not a job for T-SQL ! don't try to write reports in query analyzer. SQL Server is a not a presentation tool. use a report writer like Access or Crystal Reports which have built-in support for subtotals and grand totals, and spacing and indenting and all that. SQL Server needs to return the raw data and the presentation layer should format and calculate totals.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources