All Forums Database
gkk 22 posts Joined 11/10
01 Mar 2012
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:
Stefans 38 posts Joined 02/12
01 Mar 2012

Check the below link for more info....

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

Stalin

karthik_2244 36 posts Joined 12/10
01 Mar 2012

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

karthik_2244 36 posts Joined 12/10
01 Mar 2012
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 22 posts Joined 11/10
17 Apr 2012

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 22 posts Joined 11/10
17 Apr 2012

 

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

04 Jul 2014

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;
 

dnoeth 4628 posts Joined 11/04
05 Jul 2014

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.
 

Dieter

You must sign in to leave a comment.