All Forums Database
uco 15 posts Joined 12/13
05 Dec 2013
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

 

 
 

M.Saeed Khurram 544 posts Joined 09/12
06 Dec 2013

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 15 posts Joined 12/13
06 Dec 2013

Thank you Khurram, It is helpful

M.Saeed Khurram 544 posts Joined 09/12
06 Dec 2013

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

Khurram

gerardo 13 posts Joined 10/09
09 Dec 2013

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 

You must sign in to leave a comment.