(ANSI) SQL QUERY FOR AGGREGATED DATA-CodePudding

PROBLEM

SALES_TABLE has 3 columns: SALES_DIVISION, STATE, AND SALES_AMT

The specs are:

create a column called REGION with valid values 'EASTERN', 'CENTRAL', 'PACIFIC' or NULL by default:

set to EASTERN if state = NY or MA
set to CENTRAL if state = IL or TX
set to PACIFIC if state = CA, OR, or WA

we want to group the data by SALES_DIVISION, REGION, and STATE
create columns EAST, CENTRAL, and PAC with values of 1 or 0:

if the aggregated SALES_AMT > 0 and REGION = EASTERN then set column EAST to 1 else 0
if the aggregated SALES_AMT > 0 and REGION = CENTRAL then set column CENTRAL to 1 else 0
if the aggregated SALES_AMT > 0 and REGION = PACIFIC then set column PACIFIC to 1 else 0

Below is the query that I built from the sales table, grouping the data by SALES_DIVISION, REGION, and STATE:

The above query produces something like this:

SALES_DIVISON	REGION	STATE	EAST	CENTRAL	PAC	EAST_CENTRAL	CENTRAL_PACIFIC
DIV_01	EASTERN	NY	1	0	0	1	0
DIV_01	EASTERN	MA	1	0	0	1	0
DIV_01	CENTRAL	TX	0	1	0	1	1
DIV_02	PACIFIC	CA	0	0	1	0	1
DIV_03	CENTRAL	TX	0	1	0	1	1
DIV_03	PACIFIC	WA	0	0	1	0	1

HOWEVER, the end user wants us to provide an AGGREGATED RESULT as follows:

SALES_DIVISON	REGION	STATE	EAST	CENTRAL	PAC	EAST_CENTRAL	CENTRAL_PACIFIC
DIV_01	-	-	1	1	0	3	1
DIV_02	-	-	0	0	1	0	1
DIV_03	-	-	0	1	1	1	2

SPECIFICATIONS for the AGGREGATED RESULT:

leave the REGION and STATE as blank
even though we say "aggregate" - the EAST , CENTRAL, AND PAC values will STILL remain 0 or 1. So in the case of "DIV_01", which has two EASTERN states, we want the EAST column to still show 1 instead of 2 in the AGGREGATED RESULT.
The EAST_CENTRAL and CENTRAL_PACIFIC values should be the aggregate of the query result #1. In the case of "DIV_01" where we have two EASTERN regions - though the EAST column shows only 1 (as required by SPEC #1), the EAST_CENTRAL column value should show value of 3 because we have 2 eastern states plus 1 central state.

QUESTION: how do I create a SELECT statement to get the aggregated result? Would it be possible to do a SELECT statement out of the SELECT -GROUP BY statement?

CodePudding user response：

You could use a CTE (Common Table Expression) based on your first query, and then write derivative queries off of that.

WITH sales_cte as (your_select_query_here)

select sales_division, NULL as State, NULL as Region, sum(East), sum(Central), sum(Pac), sum(East_Central), sum(Central_Pacific)
from sales_cte
group by sales_division;