We have
learnt using WITH and WITH BY functions earlier.
- WITH BY is used to create subtotals and >
- WITH is used to create grand totals.
BUT problem with using these
functions is that WITH and WITHBY are both Teradata extensions . They are not
ANSI standards.
Also we
know that we cannot use WITH and WITH BY to get relational result.
Hence
we make use of COUNT window functions to create subtotals or grand totals.
COUNT window is the ANSI
standard.
Generating SUBTOTALs-- Using
WITH ...BY and using ANSI Standard COUNT function
Following
is the Teradata way of generating the subtotals using the WITH..BY
select
departmentNo, Employeeid, Salary
FROM
EMPLOYEE
WITH
COUNT(departmentNo) BY departmentno;
*** Query completed. 18 rows found. 3 columns
returned.
*** Total elapsed time was 1 second.
DepartmentNo Employeeid Salary
------------ -----------
----------
100 1 1000.00
100 9 9000.00
100 5 5000.00
------------
3
200 124
12345.11
200 2 2000.00
200 6 6000.00
200 10
10000.00
------------
4
300 3 3000.00
300 7 7000.00
300 11
11000.00
------------
3
400 8 8000.00
400 4 4000.00
400 12
12000.00
400 144
12345.11
------------
4
The
result is non relational and hence this query cannot be run using SQL
assistant.
We need
to run this query using BTEQ.
We can
get the similar result using ANSI standard COUNT OVER function.
select
departmentNo,
Employeeid,
Salary,
COUNT(*) OVER
(PARTITION BY departmentNO
ROWS BETWEEN
UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING)
FROM
EMPLOYEE
;
DepartmentNo
|
Employeeid
|
Salary
|
Group Count(*)
|
100
|
5
|
5000
|
3
|
100
|
1
|
1000
|
3
|
100
|
9
|
9000
|
3
|
200
|
124
|
12345.11
|
4
|
200
|
2
|
2000
|
4
|
200
|
6
|
6000
|
4
|
200
|
10
|
10000
|
4
|
300
|
3
|
3000
|
3
|
300
|
11
|
11000
|
3
|
300
|
7
|
7000
|
3
|
400
|
12
|
12000
|
4
|
400
|
8
|
8000
|
4
|
400
|
4
|
4000
|
4
|
400
|
144
|
12345.11
|
4
|
Note
that this Output is relational and can be run in SQL assistant.
What does the PARTITION BY do?
The
'PARTITION BY ' clause defines the group in which the rows are to be counted.
It does
the same function that 'BY' clause does in the teradata way of getting
subtotals- .i.e Establishing Groups.
The
count gets showed in each row within a group and hence appear multiple times.
COUNT
Window function and standard aggregate functions (.ie SUM,AVG,COUNT,MIN,MAX)
may not be used in same select.
Generating Grand TOTALs--
Using WITH ...BY and using ANSI Standard COUNT function
Following
is the Teradata Way of generating grand totals using WITH
select
departmentNo,
Employeeid,
Salary
FROM EMPLOYEE
WITH
COUNT(departmentNo);
*** Query completed. 15 rows found. 3 columns
returned.
*** Total elapsed time was one minute and 4
seconds.
DepartmentNo Employeeid Salary
------------ -----------
----------
200 124
12345.11
400 8 8000.00
200 2 2000.00
200 6 6000.00
400 4 4000.00
400 12
12000.00
100 1 1000.00
300 3 3000.00
200 10
10000.00
300 7 7000.00
300 11
11000.00
100 9 9000.00
100 5 5000.00
400 144
12345.11
------------
14
The
result is non relational and hence this query cannot be run using SQL
assistant.
We need
to run this query using BTEQ.
We can
get the similar result using ANSI standard COUNT OVER function.
select
departmentNo,
Employeeid,
Salary,
COUNT(*) OVER(
ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM
EMPLOYEE
;
DepartmentNo
|
Employeeid
|
Salary
|
Group Count(*)
|
100
|
1
|
1000
|
14
|
100
|
5
|
5000
|
14
|
100
|
9
|
9000
|
14
|
200
|
2
|
2000
|
14
|
200
|
6
|
6000
|
14
|
200
|
10
|
10000
|
14
|
200
|
124
|
12345.11
|
14
|
300
|
3
|
3000
|
14
|
300
|
7
|
7000
|
14
|
300
|
11
|
11000
|
14
|
400
|
4
|
4000
|
14
|
400
|
8
|
8000
|
14
|
400
|
12
|
12000
|
14
|
400
|
144
|
12345.11
|
14
|
As we
can see the total count is output on each row.
No comments:
Post a Comment