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
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
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.
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
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
