SQL Sub Queries

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

SQL Sub Queries

Hi 

 

how to write sql script with subtotals for every column in table.

County State City Amount
USA GA Atlanta 10$
USA GA Buckhead 20$
USA GA Hapeville 30$
USA GA Sandy springs 40$
USA GA Subtotal 100$
USA MN Shoreview 11
USA MN St.Paul 22
USA MN Minneapolis 33
USA MN Roseville 44
USA MN Bloomington 55
USA MN Subtotal 165
USA ALL ALL 265$


Accepted Solutions
Senior Apprentice

Re: SQL Sub Queries

This is a task for GROUPING SETS OR ROLLUP

1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: SQL Sub Queries

This is a task for GROUPING SETS OR ROLLUP

Fan

Re: SQL Sub Queries

Hello 

Appreciate for response  I took sample query and played little bit our data set, 

 

in grouping can we concatenate state column value  +'ALL'   easily identifies row total

 

 SELECT
Year,
Month No , 
 CASE GROUPING(state)
             WHEN 1
             THEN '(state || -all-)'
             ELSE state
            END AS state,
 
                  SUM(margin)
     FROM sales_view
     GROUP BY Year,Month No,
ROLLUP (state, county, city);