SQL hierarchical summary and SQL Classification

SQL hierarchical summary and SQL Classification

-- Test Data create table tb ([DB-ID] varchar (10), ENTITY varchar (10), DATE varchar (10), [CUST-NO] int, AMOUNT decimal ), TAX decimal (2004) insert tb select 'rchq', '001', '2017-11-10 ', 200000,100.00, 17.00 union all select 'rchq', '001 ', '2017-11-10', 2004, 200000,200.00 union all select 'rchq', '001', '2017-11-12 ', 34.00, 2004 union all select 'rchq', '002 ', '2017-11-10', 2004, 200000,100.00 union all select 'rchq', '002 ', '2017-11-10', 17.00, 2004 union all select 'rchq', '002 ', '1970-11-12 ', 2004, 25.50go -- Query select [DB-ID], ENTITY, DATE, [CUST-NO], AMOUNT, TAXfrom (select [DB-ID] = case when grouping ([DB-ID]) = 1 then' total 'else [DB-ID] end, ENTITY = case when grouping ([DB-ID]) = 1 then ''when grouping (ENTITY) = 1 then' subtotal 'else ENTITY end, DATE = case when grouping ([DB-ID]) = 1 then'' when grouping (ENTITY) = 1 then ''when grouping (DATE) = 1 then' subtotal 'else DATE end, [CUST-NO] = case when grouping ([DB-ID]) = 1 then ''when grouping (ENTITY) = 1 then'' when grouping (DATE) = 1 then'' when grouping ([CUST-NO]) = 1 then 'subtotal 'else cast ([CUST-NO] as varchar) end, AMOUNT = sum (AMOUNT), TAX = sum (TAX), s1 = grouping ([DB-ID]), s2 = [DB-ID], s3 = grouping (ENTITY), s4 = ENTITY, s5 = grouping (DATE), s6 = DATE, s7 = grouping ([CUST-NO]), s8 = [CUST-NO] from tb group by [DB-ID], ENTITY, DATE, [CUST-NO] with rollup having grouping ([CUST-NO]) = 1 union all select [DB-ID], ENTITY, DATE, cast ([CUST-NO] as varchar), AMOUNT, TAX, s1 = 0, s2 = [DB-ID], s3 = 0, s4 = ENTITY, s5 = 0, s6 = DATE, s7 = 0, s8 = [CUST-NO] from tb) a order by s1, s2, s3, s4, s5, s6, s7, s8go -- delete test drop table tb/** DB-IDENTITYDATECUST-NOAMOUNTTAXRCHQ0012004-11-10200000100.0017.00RCHQ0012004-11-10200000200.0034.00RCHQ0012004-11-10 subtotal sort-keys subtotal 150.0025.50RCHQ001 subtotal sort-keys subtotal sort .0076.50rchq subtotal 900.00153.00 total 900.00153.00 */

SQL classification query and summary

Understand

First category
Select Supplier name, count (name), recipe name, recipe category, sum (recipe unit price), sum (Quantity) from cpxxtb group by supplier name, recipe name, recipe category

Second category
If it is based on supplier statistics, you can write it:

Select Supplier name, count (name), sum (recipe unit price), sum (Quantity) from cpxxtb group by supplier name

Total:
Select sum (recipe unit price), sum (Quantity) from cpxxtb

OVER

SQL Classification summary

Standard answer:

Select a. Dc_ID, a. Dc_name,
Sum (case when use_status = 'in use 'then 1 else 0 end) in use,
Sum (case when use_status = 'not used 'then 1 else 0 end) not used
From a, B where a. Dc_ID = B. Dc_ID
Group by a. Dc_ID, a. Dc_name