Before we learn Totals and
Subtotals we first create a test table using below queries:
CREATE TABLE MY_TEST_DB.SALES_TABLE,
NO FALLBACK
(
Product_id INTEGER,
SALE_DATE DATE,
DAILY_SALES DECIMAL(10,2)
)PRIMARY INDEX(PRODUCT_ID)
INDEX(SALE_DATE);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '09/28/2000' (DATE,FORMAT
'MM/dd/yyyy'), 48850.40);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '09/29/2000' (DATE,FORMAT
'MM/dd/yyyy'), 54500.22);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '09/30/2000' (DATE,FORMAT
'MM/dd/yyyy'), 36000.07);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '10/01/2000'
(DATE,FORMAT
'MM/dd/yyyy'), 40200.43);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '10/02/2000' (DATE,FORMAT
'MM/dd/yyyy'), 32800.50);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '10/03/2000' (DATE,FORMAT
'MM/dd/yyyy'), 64300.00);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(1000 , '10/04/2000' (DATE,FORMAT
'MM/dd/yyyy'), 54553.10);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '09/28/2000' (DATE,FORMAT
'MM/dd/yyyy'), 41888.88);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '09/29/2000' (DATE,FORMAT
'MM/dd/yyyy'), 48000.00);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '09/30/2000' (DATE,FORMAT
'MM/dd/yyyy'), 49850.03);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '10/01/2000' (DATE,FORMAT
'MM/dd/yyyy'), 54850.29);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '10/02/2000' (DATE,FORMAT
'MM/dd/yyyy'), 36021.93);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '10/03/2000' (DATE,FORMAT
'MM/dd/yyyy'), 43200.18);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(2000 , '10/04/2000' (DATE,FORMAT
'MM/dd/yyyy'), 32800.50);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '09/28/2000' (DATE,FORMAT
'MM/dd/yyyy'), 61301.77);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '09/29/2000' (DATE,FORMAT
'MM/dd/yyyy'), 34509.13);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '09/30/2000' (DATE,FORMAT
'MM/dd/yyyy'), 43868.86);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '10/01/2000' (DATE,FORMAT
'MM/dd/yyyy'), 28000.00);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '10/02/2000' (DATE,FORMAT
'MM/dd/yyyy'), 19678.94);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '10/03/2000' (DATE,FORMAT
'MM/dd/yyyy'), 21553.79);
INSERT INTO MY_TEST_DB.SALES_TABLE
VALUES(3000 , '10/04/2000' (DATE,FORMAT
'MM/dd/yyyy'), 15675.33);
·
WITH
.. BY:
·
WITH.. BY is a Teradata extension (
not allowed in ANSI) . This feature works only from BTEQ and not in SQL assistant(ODBC
clients).
·
WITH… BY is different
from GROUP BY in the fact that with GROUP BY detail rows are eliminated. But
with WITH .. BY detail rows are not eliminated.
·
WITH..BY allows us to
provide sub total(or sub-counts, sub - averages) breaks on more than 1 column.
·
Also note that WITH ..
BY will automatically
generate SORT by the columns mentioned in the BY clause.
Example:
|
select
product_id,sale_date,daily_sales from sales_table with SUM(daily_sales) by
product_id;
*** Query completed. 24 rows found. 3
columns returned.
*** Total elapsed time was 1 second.
Product_id
SALE_DATE DAILY_SALES
----------- ----------
------------
1000
2000-09-30 36000.07
1000
2000-10-02 32800.50
1000
2000-10-03 64300.00
1000
2000-10-04 54553.10
1000
2000-10-01 40200.43
1000
2000-09-29 54500.22 --> detail row
1000
2000-09-28 48850.40 --> detail row
------------
Sum(DAILY_SALES) 331204.72 --> Subtotal
2000
2000-09-30 49850.03
2000
2000-10-02 36021.93
2000
2000-10-03 43200.18
2000
2000-10-04 32800.50
2000
2000-10-01 54850.29
2000
2000-09-29 48000.00
2000
2000-09-28 41888.88
------------
Sum(DAILY_SALES) 306611.81
3000
2000-09-30 43868.86
3000
2000-10-02 19678.94
3000
2000-10-03 21553.79
3000
2000-10-04 15675.33
3000
2000-10-01 28000.00
3000
2000-09-29 34509.13
3000
2000-09-28 61301.77
------------
Sum(DAILY_SALES) 224587.82
Note that the result is automatically
sorted by the column product_id.(column in BY clause). Also note that we can
see detail rows along with subtotals.
|
Running the above query in SQL ASSISTANT (Teradata V13) gives us
the detail rows ordered by product_id.
However subtotals are not visible:
·
WITH
…. BY multiple aggregates
Multiple aggregates within the same WITH BY clause are possible
if all aggregates are based on the same 'break' column.
Example:
|
select
product_id,sale_date,daily_sales from sales_table
with
SUM(daily_sales)
,
AVG(daily_sales),
MAX(Daily_sales)
by product_id;
*** Query completed. 24 rows found. 3
columns returned.
*** Total elapsed time was 2 seconds.
Product_id
SALE_DATE DAILY_SALES
----------- ----------
------------
1000
2000-09-30 36000.07
1000
2000-10-02 32800.50
1000
2000-10-03 64300.00
1000
2000-10-04 54553.10
1000
2000-10-01 40200.43
1000
2000-09-29 54500.22
1000
2000-09-28 48850.40
------------
------------
------------
Sum(DAILY_SALES) 331204.72
Average(DAILY_SALES) 47314.96
Maximum(DAILY_SALES) 64300.00
2000
2000-09-30 49850.03
2000
2000-10-02 36021.93
2000
2000-10-03 43200.18
2000
2000-10-04 32800.50
2000
2000-10-01 54850.29
2000
2000-09-29 48000.00
2000
2000-09-28 41888.88
------------
------------
------------
Sum(DAILY_SALES) 306611.81
Average(DAILY_SALES)
43801.69
Maximum(DAILY_SALES)
54850.29
3000
2000-09-30 43868.86
3000 2000-10-02 19678.94
3000
2000-10-03 21553.79
3000
2000-10-04 15675.33
3000
2000-10-01 28000.00
3000
2000-09-29 34509.13
3000
2000-09-28 61301.77
------------
------------
------------
Sum(DAILY_SALES) 224587.82
Average(DAILY_SALES)
32083.97
Maximum(DAILY_SALES)
61301.77
|
·
WITH
..BY with multiple columns in BY clause:
We can specify multiple columns as the break columns. If either
of the column changes the subtotal value is printed.
|
select product_id,extract(MONTH FROM
sale_date) AS mon_sale_date,daily_sale
s from sales_table
with
SUM(daily_sales) (TITLE 'SUM ') by product_id, extract(MONTH
FROM sale_dat
e) ;
*** Query completed. 27 rows found. 3
columns returned.
*** Total elapsed time was 1 second.
Product_id
mon_sale_date DAILY_SALES
----------- ------------- ------------
1000 9 36000.07
1000 9 54500.22
1000 9 48850.40
------------
SUM 139350.69
1000 10 54553.10
1000 10 64300.00
1000 10 32800.50
1000 10 40200.43
------------
SUM 191854.03
2000 9 49850.03
2000 9 48000.00
2000 9 41888.88
------------
SUM 139738.91
2000 10 32800.50
2000 10 43200.18
2000 10 36021.93
2000 10 54850.29
------------
SUM 166872.90
3000 9 43868.86
3000 9 61301.77
3000 9 34509.13
------------
SUM 139679.76
3000 10 15675.33
3000 10 21553.79
3000 10 19678.94
3000 10 28000.00
------------
SUM 84908.06
|
The above result is sorted by PRODUCT_ID (ASC) and MONTH(ASC).
If Either of the column value changes a sub total gets printed.
·
WITH
… BY with multiple Subtotal levels
We can use multiple 'WITH BY' clauses in an SQL to create
multiple subtotal levels.
|
select
product_id,
extract(MONTH FROM sale_date) AS
mon_sale_date,
daily_sales from sales_table
WITH
SUM(daily_sales) (TITLE 'SUM
MONTH') by extract(MONTH FROM
sale_date)
WITH
SUM(daily_sales) (TITLE 'SUM PRODUCT
ID') by product_id;
*** Query completed. 30 rows found. 3
columns returned.
*** Total elapsed time was 1 second.
Product_id
mon_sale_date DAILY_SALES
----------- ------------- ------------
1000
9 36000.07
1000 9 48850.40
1000 9 54500.22
------------
SUM MONTH 139350.69 --> SUM FOR product_id=1000 and month 9
1000
10 54553.10
1000 10 64300.00
1000 10 32800.50
1000 10 40200.43
------------
SUM MONTH 191854.03 --> SUM FOR product_id=1000 and month 10
------------
SUM PRODUCT ID 331204.72 --> SUM for all months for product_id=1000
2000 9 49850.03
2000 9 41888.88
2000 9 48000.00
------------
SUM MONTH 139738.91
2000 10 32800.50
2000 10 43200.18
2000 10 36021.93
2000 10 54850.29
------------
SUM MONTH 166872.90
------------
SUM PRODUCT ID 306611.81
3000 9 43868.86
3000 9 34509.13
3000 9 61301.77
------------
SUM MONTH 139679.76
3000 10 15675.33
3000 10 21553.79
3000 10 19678.94
3000 10 28000.00
------------
SUM MONTH 84908.06
------------
SUM PRODUCT ID 224587.82
Note that the result is 1st sorted by the column in
outer BY .ie product_id and then by the inner BY .ie extracted month.
Thus when using multiple
WITH BY , the column in OUTER BY becomes the major sort sequence and the
inner ones become minor sort sequence.
|
·
WITH
clause (without BY)
BY clause is used for creating breaks and printing sub totals.
If we don’t use BY, we will not get subtotals, but WITH clause
will print the Grand Total/GRAND average/grand total
|
select product_id,extract(MONTH FROM
sale_date) AS mon_sale_date,daily_sale
s from sales_table
with
SUM(daily_sales) (TITLE 'SUM ') ;
*** Query completed. 22 rows found. 3
columns returned.
*** Total elapsed time was 2 seconds.
Product_id
mon_sale_date DAILY_SALES
----------- ------------- ------------
2000 9 49850.03
2000 10 36021.93
2000 10 43200.18
2000 10 32800.50
2000 10 54850.29
2000 9 48000.00
2000 9 41888.88
1000 9 36000.07
1000 10 32800.50
1000 10 64300.00
1000 10 54553.10
1000 10 40200.43
1000 9 54500.22
1000 9 48850.40
3000 9 43868.86
3000 10 19678.94
3000 10 21553.79
3000 10 15675.33
3000 10 28000.00
3000 9 34509.13
3000 9 61301.77
------------
SUM 862404.35 --> Grand total
|
Note that when we use BY clause the result is sorted by that
column.
However above column is not sorted by any means.
However we can use the following query to get the results sorted
.
select product_id,extract(MONTH FROM sale_date) AS
mon_sale_date,daily_sales from sales_table
with
SUM(daily_sales) (TITLE 'SUM ')
order by 1;
·
DISTINCT
modifier:
Use the DISTINCT modifier is used in conjuction with
the COUNT aggregate to prevent the same value from being counted more than
once.
Example:
|
select product_id,extract(MONTH FROM
sale_date) AS mon_sale_date,daily_sale
s from sales_table
with
count(product_id) (TITLE 'COUNT OF PRODUCT')
order by 1;
*** Query completed. 22 rows found. 3
columns returned.
*** Total elapsed time was 2 seconds.
Product_id
mon_sale_date DAILY_SALES
----------- ------------- ------------
1000 9 36000.07
1000 10 32800.50
1000 10 64300.00
1000 10 54553.10
1000 10 40200.43
1000 9 54500.22
1000 9 48850.40
2000 9 49850.03
2000 10 36021.93
2000 10 43200.18
2000
10 32800.50
2000 10 54850.29
2000 9 48000.00
2000 9 41888.88
3000 9 43868.86
3000 10 19678.94
3000 10 21553.79
3000 10 15675.33
3000 10 28000.00
3000 9 34509.13
3000 9 61301.77
-----------
21
|
Above query gives number of products. However problem is that
same product is counted multiple times.
Hence this is not the unique counts of products.
To get the number of unique products we use the following query:
|
select product_id,extract(MONTH FROM
sale_date) AS mon_sale_date,daily_sale
s from sales_table
with
count(distinct product_id) (TITLE 'COUNT OF PRODUCT')
order by 1;
*** Query completed. 22 rows found. 3
columns returned.
*** Total elapsed time was 1 second.
Product_id
mon_sale_date DAILY_SALES
----------- ------------- ------------
1000 9 36000.07
1000 10 32800.50
1000 10 64300.00
1000 10 54553.10
1000 10 40200.43
1000 9 54500.22
1000 9 48850.40
2000 9 49850.03
2000 10 36021.93
2000 10 43200.18
2000 10 32800.50
2000 10 54850.29
2000 9 48000.00
2000 9 41888.88
3000 9 43868.86
3000 10 19678.94
3000 10 21553.79
3000 10 15675.33
3000 10
28000.00
3000 9 34509.13
3000 9 61301.77
-----------
3
|
Note : When using
DISTINCT with an aggregate function only a single column or expression may be
used.
·
Combining
WITH and WITH BY:
We can combine WITH and WITH BY in same query to obtain
subtotals as well as grand totals.
|
select
daily_sales,product_id,extract(MONTH FROM sale_date) AS mon_sale_dat
e from sales_table
With SUM(daily_sales) (TITLE 'SUM OF PRODUCT') BY PRODUCT_ID
WITH SUM(daily_sales) --> WITH should be after all the WITH BY
order by 3;
*** Query completed. 25 rows found. 3
columns returned.
*** Total elapsed time was 1 second.
DAILY_SALES
Product_id mon_sale_date
------------ -----------
-------------
54500.22 1000 9
36000.07 1000 9
48850.40 1000 9
64300.00 1000 10
54553.10 1000 10
32800.50 1000 10
40200.43 1000 10
------------
331204.72 --> Sub total
48000.00 2000 9
49850.03 2000 9
41888.88 2000 9
43200.18 2000 10
32800.50 2000 10
36021.93 2000 10
54850.29 2000 10
------------
306611.81 -->
Sub total
34509.13 3000 9
43868.86 3000 9
61301.77 3000 9
21553.79 3000 10
15675.33 3000 10
19678.94 3000 10
28000.00 3000 10
------------
224587.82 --> Sub total
------------
862404.35 --> Grand Total
|
Note : Above query has a
ORDER BY clause. The WITH BY clause controls the high level sort .i.e.
(PRODUCT_ID) and ORDER BY clause controls the minor sort sequence .i.e.
(extracted month)
·
WITH
and WITH BY rules in brief:
WITH summary-list BY break-list ASC/DESC:
1. Above is used to provide SUB-totals,
sub-counts or sub-averages along with the display of detail rows.
2. Summary list can contain multiple columns
WITH
SUM(SALARY),AVG(Salary) BY dept_name
3. Break list can contain multiple columns
WITH SUM(salary) BY dept_no,Salary_code
If either of dept_no or Salary_code changes the
sub-total/count/average is printed.
4. By default the result is shown in the ORDER
specified by the columns in BY clause.(break list columns).
We can control ordering sequence.
5. If the query contains WITH .. BY and ORDER..
BY then WITH .. BY determines major sort key and ORDER BY specifies any
additional minor sorts keys.
6. A SQL statement can have multiple WITH .. BY
clauses.
7. When we have multiple WITH .. BY clauses the last (outer) WITH .. BY clause is the
highest sort sequence.
WITH Sum(salary) with product_sub_code
WITH SUM(salary) WITH product_id
Here product_id is the major sort key and product_sub_code is
the minor sort key.
WITH summary-list
1. Used to provide grand totals, counts or
averages
2. Summary list can contain multiple columns.
3. Note that a query can contain only one WITH
. (unlike WITH... BY which can be used multiple times)
4. When a query contains both WITH and WITH BY
clause , WITH Should be written after all the WITH .. BY
No comments:
Post a Comment