Teradata sql grouping

Database
uco
Enthusiast

Teradata sql grouping

Hi 

I have source data froma a table in the following way 

Source  Data table 

Date_key          Location       Vendor           call_cnt

2013-09-09      Highlands         att               1500

2013-10-28      Highlands       att                 200

2013-09-26       Utah              att                   800

2013-08-28      Cupertino      verizon             200

2013-09-30      Cupertino        Verizon          200

2013-09-30     San jose       Verizon             400

2013-09-28      Cupertino     Verizon               600

I want the output as 

output xls 

att     Location       september   October        2013Total

        Highlands        1500            200              1700

             utah             800              0                 800

                                  ------            ---              ---

att Totals               2300             200              1500

Verizon  Cupertino       200       600               800

              Sanjose          600        0                 600

                                  ----           ----              ---

 Verizon totalss          800       600              1400

Any help how to get this output ?

thanks


Tags (2)
4 REPLIES
Enthusiast

Re: Teradata sql grouping

Hi,

You can use the following query to get all the aggregations. But to get report in this particular format you might have to use some reporting tool. 

CT Test 
(
Date_key DATE
,Location VARCHAR(10)
,Vendor VARCHAR(10)
,call_cnt INT
)
PRIMARY INDEX (Date_key);

INS INTO Test('2013-09-09','Highlands','att' ,1500 );
INS INTO Test('2013-10-28','Highlands','att' ,200 );
INS INTO Test('2013-09-26','Utah ','att' ,800 );
INS INTO Test('2013-08-28','Cupertino','verizon',200 );
INS INTO Test('2013-09-30','Cupertino','Verizon',200 );
INS INTO Test('2013-09-30','San jose ','Verizon',400 );

SELECT VENDOR, LOCATION, Date_key (FORMAT 'MMMM') , CALL_CNT, SUM(CALL_CNT) OVER (PARTITION BY VENDOR, LOCATION ORDER BY LOCATION) AS YearTotal,
SUM(CALL_CNT) OVER (PARTITION BY VENDOR ORDER BY LOCATION) AS VendorTotal
FROM Test;
Khurram
uco
Enthusiast

Re: Teradata sql grouping

Thank you Khurram, It is helpful

Enthusiast

Re: Teradata sql grouping

welcome uco, Just for my interest, which tool you are using to make these reports?

Khurram
Teradata Employee

Re: Teradata sql grouping

To obtain ththe output as you have indicated, the sql:

SEL VENDOR,

COALESCE(LOCATION,'TOTAL ') AS LOCATION ,

SEPT,

OCT,

TOTAL2013

FROM (

SEL VENDOR,

LOCATION,

SUM(CASE WHEN EXTRACT (MONTH FROM DATE_KEY) =9 THEN CALL_CNT ELSE 0 END) AS SEPT ,

SUM(CASE WHEN EXTRACT(MONTH FROM DATE_KEY) =10 THEN CALL_CNT ELSE 0 END) AS OCT,

SEPT + OCT AS TOTAL2013

FROM DW_USUARIO.TEST

GROUP BY GROUPING SETS((1),(1,2))

) AS AA

ORDER BY VENDOR , COALESCE(LOCATION,'ZZZZ'):

The result is_:

Vendor LOCATION SEPT OCT TOTAL2013

att       Highlands 1500 200  1700

att       Utah         800   0   800

att      TOTAL      2300 200  2500

Verizon Cupertino   200   0   200

Verizon San jose    400   0   400

Verizon TOTAL       600   0   600