|
View previous topic :: View next topic
|
| Author |
Message |
mbattu
New User
Joined: 19 Jan 2011
Posts: 15
Location: Toronto
|
|
|
|
Hi,
I have a set of companies that have to be fetched from a Table. This table can have multiple rows, single row or no row for a given Company. Therefore requirement is that if data is present in the Table, display Company & Count(rows). If not still display the Company with count 0.
Example:-
Input given
AAA1
BBB2
CCC3
DDD4
Example Data in Table say Company_TBL
-------------------------------
Col1 Col2 Col3
AAA1 10 20210101
AAA2 20 20210115
CCC3 11 20210201
DDD4 05 20210201
Desired Output:-
-----------------------
AAA1 2
BBB2 0
CCC3 1
DDD4 1
To accomplish this my approach was to write two sub-queries, first one to fetch the details from COMPANY_TBL and other is to declares all the companies as constants with value zero. Perform UNION of both.
Problem with this solution is I am not sure of DB2 equivalent of below PostgreSQL. I can write single row constants for each Company records using SYSIBM.SYSDUMMY1 but I want to know if there is any better way to this.
| Code: |
SELECT *
FROM ( VALUES
('AAA1', 0),
('BBB2', 0),
('CCC3', 0),
('DDD4', 0)
) AS TEMP_TBL (COL1, COL2)
; |
Below is the complete query I am thinking of
| Code: |
SELECT COL1
,SUM(COL2)
FROM (
SELECT COL1
,COUNT(COL1)
FROM COMPANY_TBL
WHERE COL1 IN ('AAA1', 'BBB2', 'CCC3', 'DDD4')
GROUP BY COL1
UNION
SELECT *
FROM ( VALUES
('AAA1', 0),
('BBB2', 0),
('CCC3', 0),
('DDD4', 0)
) AS TEMP_TBL (COL1, COL2
)
GROUP BY COL1
; |
|
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
How did you get AAA1 2? Second ,what is the input , is it always constant ?
You can do a left join on the Company_TBL , ( by adding those hardcoded values in the where) and then do a Group By and Count (*) on main SELECT, make sure to handle NULL to 0. |
|
| Back to top |
|
|
mbattu
New User
Joined: 19 Jan 2011
Posts: 15
Location: Toronto
|
|
|
|
| Rohit Umarjikar wrote: |
| How did you get AAA1 2? Second ,what is the input , is it always constant ? |
Thank you for the reply.
Sorry, I gave wrong COMPANY_TBL data, corrected it now. Yes, input COMPANY list is constant.
| Code: |
Col1 Col2 Col3
AAA1 10 20210101
AAA1 20 20210115
CCC3 11 20210201
DDD4 05 20210201 |
| Rohit Umarjikar wrote: |
| You can do a left join on the Company_TBL , ( by adding those hardcoded values in the where) and then do a Group By and Count (*) on main SELECT, make sure to handle NULL to 0. |
I didn't get this part. How do I bring those hardcoded value to resource section by adding them in condition section (WHERE).
For now I am doing this way which I feel is not good
| Code: |
SELECT COL1
,SUM(COL2)
FROM (
SELECT COL1
,COUNT(COL1)
FROM COMPANY_TBL
WHERE COL1 IN ('AAA1', 'BBB2', 'CCC3', 'DDD4')
GROUP BY COL1
UNION
SELECT 'AAA1', 0 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'BBB2', 0 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'CCC3', 0 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'DDD4', 0 FROM SYSIBM.SYSDUMMY1
)
GROUP BY COL1
; |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
| Code: |
SELECT COMPANY_TBL.col1, count(*) FROM
(SELECT 'AAA1' as col1,10,'20210101' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'AAA1' as col1,20,'20210115' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'CCC3' as col1,11,'20210201' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'DDD4' as col1,05,'20210201' FROM SYSIBM.SYSDUMMY1) COMPANY_TBL
GROUP BY COMPANY_TBL.col1
UNION
SELECT static_tbl.col1,0 FROM
(SELECT 'AAA1' as col1 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'BBB2'as col1 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'CCC3'as col1 FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'DDD4'as col1 FROM SYSIBM.SYSDUMMY1) static_tbl
WHERE NOT EXISTS (
( SELECT 1 FROM
(SELECT 'AAA1' as col1,10,'20210101' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'AAA1' as col1,20,'20210115' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'CCC3' as col1,11,'20210201' FROM SYSIBM.SYSDUMMY1
UNION
SELECT 'DDD4' as col1,05,'20210201' FROM SYSIBM.SYSDUMMY1) COMPANY_TBL1
where static_tbl.col1 = COMPANY_TBL1.col1
)
)
ORDER BY 1,2 |
Output:
| Code: |
COL1 2
---- -
AAA1 2
BBB2 0
CCC3 1
DDD4 1 |
|
|
| Back to top |
|
|
|
|
|
