Counts and total count in one query ?

Database
gkk
N/A

Counts and total count in one query ?

Hi ,

How to display count and sub counts in select query itself ?

source data set :

table 1 :

cat1 = 101

cat2 = 102

cat3 = 103

table 2 :

Id     Date            catg

12 2012-01-02 101

12 2012-01-02 101

like 9 records

12 2012-01-02 103

like 1 record

12  2012-01-03 102

like 3 records

12  2012-01-03 102

12 2012-01-02  103

like 5 records

Exp out put :

Id       Name         Date      Total_count    catg1   catg2   cat3

12     abc      2012-01-02    10                 9       0       1

12     abc     2012-01-03      8                  0       3       5

13     lmn     2012-01-02      5                  1       2      2

14     xyz     2012-01-05      4                   2      0      2

Please help me with SQL

Regards,

gkk

Tags (1)
7 REPLIES

Re: Counts and total count in one query ?

Check the below link for more info....

http://forums.teradata.com/forum/database/countsub-count

Re: Counts and total count in one query ?

add a new field in select with catg1+catg2+cat3 as Total_count

Re: Counts and total count in one query ?

CREATE VOLATILE TABLE main
(catg VARCHAR(6),
code INT)
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE  details
(id INT,
date_d DATE,
cat_code INT,
comme VARCHAR(8))
ON COMMIT PRESERVE ROWS;

INSERT INTO main ('CAT1',101);
INSERT INTO main ('CAT2',102);
INSERT INTO main ('CAT3',103);

INSERT INTO details(12,CURRENT_DATE,101,'a');
INSERT INTO details(12,CURRENT_DATE,101,'b');
INSERT INTO details(12,CURRENT_DATE,101,'c');
INSERT INTO details(12,CURRENT_DATE,102,'a');
INSERT INTO details(12,CURRENT_DATE,102,'b');
INSERT INTO details(12,CURRENT_DATE,102,'c');
INSERT INTO details(12,CURRENT_DATE,102,'d');
INSERT INTO details(12,CURRENT_DATE,102,'e');
INSERT INTO details(12,CURRENT_DATE,102,'f');
INSERT INTO details(12,CURRENT_DATE,103,'a');
INSERT INTO details(12,CURRENT_DATE,103,'b');
INSERT INTO details(12,CURRENT_DATE,103,'c');
INSERT INTO details(12,CURRENT_DATE,103,'d');
INSERT INTO details(12,CURRENT_DATE,103,'e');

Expected o/p

SEL id,
CASE WHEN  cod=101 THEN cnt ELSE 0 END CAT1,
CASE WHEN  cod=102  THEN cnt ELSE 0 END AS CAT2,
CASE WHEN  cod=103 THEN cnt ELSE 0 END AS CAT3,
CAT1+CAT2 +CAT3  AS total GROUP BY 1,2,3,4 FROM (
SEL
id,
b.cat_code AS cod,
COUNT(*) AS cnt
GROUP BY 1,2
 FROM main a
LEFT JOIN details b
ON code=cat_code )a

gkk
N/A

Re: Counts and total count in one query ?

small modification for the above query brings me the expected out put.

Thank you karthik . and Sorry for the delay in updating the post ..

CREATE VOLATILE TABLE main (catg VARCHAR(6), code INT)

ON COMMIT PRESERVE ROWS;

sel * from main

CREATE VOLATILE TABLE details (id INT, date_d DATE, cat_code INT,

comme VARCHAR(8))

ON COMMIT PRESERVE ROWS;

sel * from details

INSERT INTO main ('CAT1',101);

INSERT INTO main ('CAT2',102);

INSERT INTO main ('CAT3',103);

INSERT INTO details(12,'2012-01-02',101,'a');

INSERT INTO details(12,'2012-01-02',101,'b');

INSERT INTO details(12,'2012-01-02',101,'c');

INSERT INTO details(12,'2012-01-02',102,'a');

INSERT INTO details(12,'2012-01-02',102,'b');

INSERT INTO details(12,'2012-01-03',102,'c');

INSERT INTO details(12,'2012-01-03',102,'d');

INSERT INTO details(12,'2012-01-03',102,'e');

INSERT INTO details(12,'2012-01-04',102,'f');

INSERT INTO details(12,'2012-01-04',103,'a');

INSERT INTO details(12,'2012-01-04',103,'b');

INSERT INTO details(12,'2012-01-05',103,'c');

INSERT INTO details(12,'2012-01-05',103,'d');

INSERT INTO details(12,'2012-01-05',103,'e');

 

Expected o/p

 

 

 

 

SEL ID,DATE_D,SUM(CAT1),SUM(CAT2),SUM(CAT3),SUM(TOTAL) FROM

(

SEL id,date_d,

CASE WHEN cod=101 THEN cnt

ELSE 0

END CAT1,

CASE WHEN cod=102 THEN cnt

ELSE 0

END AS CAT2,

CASE WHEN cod=103 THEN cnt

ELSE 0

END AS CAT3, CAT1+CAT2 +CAT3 AS total

GROUP BY 1,2,3,4 ,5

FROM (

SEL id,date_d, b.cat_code AS cod, COUNT(*) AS cnt

GROUP BY 1,2,3

FROM main a LEFT JOIN details b

ON code=cat_code

)a

) B

GROUP BY 1,2

Regards,

gkk

gkk
N/A

Re: Counts and total count in one query ?




id date_d Sum(CAT1) Sum(CAT2) Sum(CAT3) Sum(total)
12 1/2/2012 3 2 0 5
12 1/3/2012 0 3 0 3
12 1/4/2012 0 1 2 3
12 1/5/2012 0 0 3 3

Re: Counts and total count in one query ?

Hi,

Below SELECT query i want to divide in three different phases. First SQL will retrive say 4M records, Second SQL will retreive >4M <8M records and the last SQL should retrieve >8m till End of table. This SQL i would be running in bteq. I guess Uniq identifier can help. Can someone please help me providing the 3 SQL.

SELECT

TOP 20

ACCT_NBR ||'|'||

acct_type_cd ||'|'||

acct_sub_type_cd ||'|'||

ent_curr_acct_sts_cd ||'|'||

acct_subsrptn_eff_dt ||'|'||

curr_srv_accs_nbr ||'|'||

srv_accs_id ||'|'||

orgnl_srv_dt ||'|'||

src_sys_orgnl_srv_dt ||'|'||

curr_subsrptn_sts_cd ||'|'||

CURR_IMSI ||'|'||

CURR_IMEI ||'|'||

PRD_CD ||'|'||

PRD_DESC ||'|'||

PRD_CAT_CD ||'|'||

blng_eff_dt ||'|'||

blng_end_dt ||'|'||

CAST(entry_dt_tm AS VARCHAR(20)) ||'|'||

SUBSRPTN_STS_RSN_CD ||'|'||

SUBSRPTN_STS_RSN_DESC  (title '')

FROM SBP_PREPAID_SUBS_INFO;

N/A

Re: Counts and total count in one query ?

Why don't you create a new topic instead of adding it to a totally unrelated thread.

And why do you want to split the data in three sets?

Trying to make your BTEQ export faster? Did you check what's the bottleneck?

You should better switch to a TPT Export instead, it's faster, can do delimited format without manually concatenating and can split in multiple files automatically.