Sub Total / Grouping - 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

General SQL Server Forums

New to SQL Server Programming

Sub Total / Grouping

Author

Topic

williamliston
Starting Member

3 Posts

Posted - 2010-03-12 : 15:26:09

I'm new to SQL but I'm gradually increasing my understanding of SQL. I have a script where I'd like to sub-total (in this case by State). Here is the script:

SELECT
customer_id AS 'Cust ID',
customer_name AS 'Customer Name',
mail_state AS 'State',
class_1id AS 'Class 1',
class_2id AS 'Class 2',
sum(Per1) Jan09,
sum(Per2) Feb09,
sum(Per3) Mar09,
sum(Per4) Apr09,
sum(Per5) May09,
sum(Per6) Jun09,
sum(Per7) Jul09,
sum(Per8) Aug09,
sum(Per9) Sep09,
sum(Per10) Oct09,
sum(Per11) Nov09,
sum(Per12) Dec09,

sum(Per1)+sum(Per2)+sum(Per3)+sum(Per4)+sum(Per5)+sum(Per6)+sum(Per7)+sum(Per8)+sum(Per9)+sum(Per10)+sum(Per11)+sum(Per12) AS 'Total',
(sum(Per1)+sum(Per2)+sum(Per3)+sum(Per4)+sum(Per5)+sum(Per6)+sum(Per7)+sum(Per8)+sum(Per9)+sum(Per10)+sum(Per11)+sum(Per12))/12 AS 'Average'

FROM (select case p21_sales_history_report_view.source_loc_id when null then p21_sales_history_report_view.source_loc_id else 100 end as Location_id,
p21_sales_history_report_view.customer_id,
p21_sales_history_report_view.customer_name,
p21_view_address.mail_state,
p21_sales_history_report_view.class_1id,
p21_sales_history_report_view.class_2id,
case p21_sales_history_report_view.year_and_period when 200812 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per1,
case p21_sales_history_report_view.year_and_period when 200901 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per2,
case p21_sales_history_report_view.year_and_period when 200902 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per3,
case p21_sales_history_report_view.year_and_period when 200903 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per4,
case p21_sales_history_report_view.year_and_period when 200904 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per5,
case p21_sales_history_report_view.year_and_period when 200905 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per6,
case p21_sales_history_report_view.year_and_period when 200906 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per7,
case p21_sales_history_report_view.year_and_period when 200907 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per8,
case p21_sales_history_report_view.year_and_period when 200908 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per9,
case p21_sales_history_report_view.year_and_period when 200909 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per10,
case p21_sales_history_report_view.year_and_period when 200910 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per11,
case p21_sales_history_report_view.year_and_period when 200911 then ((p21_sales_history_report_view.qty_shipped/unit_size) * p21_sales_history_report_view.sales_cost) else 0 end as Per12
FROM p21_sales_history_report_view
INNER JOIN p21_view_address ON (p21_view_address.id = p21_sales_history_report_view.customer_id)
WHERE p21_sales_history_report_view.invoice_line_uid_parent = 0)AAA

GROUP BY customer_id,
customer_name,
mail_state,
class_1id,
class_2id

ORDER BY mail_state

Now I can import this data into Excel and try sorting/grouping and manually calculate the sub-totals, but I would like to have the SQL script do the heavy lifting. I appreciate any suggestions. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 00:54:31

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

williamliston
Starting Member

3 Posts

Posted - 2010-03-15 : 08:50:08

Yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 09:41:15

how do you want subtotals to appear? also is this for use by reporting services report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

williamliston
Starting Member

3 Posts

Posted - 2010-03-16 : 09:49:31

The subtotals are to appear like so:
A1
A2
A3
Subtotal A
B1
B2
Subtotal B
etc.

I use this script to make a Microsoft Query and import the data into Excel.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 09:53:20

use GROUP BY... WITH CUBE or WITH ROLLUP options

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources