CODEWITHSUNDEEP
sqlsql-server-2014
Slavce
Slavce

Reputation: 13

How to show rows even if the criteria is not exists

I'm quite new to SQL and I need your help.

I want to SELECT all rows in column YM (yearmonth) in range 201801 - 201804 even if the criteria (001) is not exists for some months.

table pod11:

YM  code    hour   
------------------------------------
201712  005 32
201712  002 16
201712  003 24
201712  007 112
201801  001 112
201801  003 12
201801  005 24
201801  007 64
201802  001 64
201802  002 128
201803  005 32
201803  002 16
201804  003 24
201804  007 112
201804  008 86

I used this:

SELECT YM,code,hour
FROM pod11
WHERE  YM >= '201801' AND YM <= '201804' and code = '001'
GROUP by YM,code,hour
ORDER BY YM

I got this:

YM  code    hour   
------------------------------------
201801  001 112
201802  001 64

Any suggestions on how I could write a query to achieve this?

YM  code    hour   
------------------------------------
201801  001 112
201802  001 64
201803  0   0
201804  0   0

Upvotes: 1

Views: 100

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

I was wondering if this could be done without subqueries. I think it can be:

select top (1) with ties YM,
       (case when code = '001' then hour else 0 end) as code,
       (case when code = '001' then code else 0 end) as hour
from pod11
where YM between '201801' and '201804' 
order by rank() over (partition by ym
                      order by (case when code = '001' then 1 else 2 end),
                               (case when code <> '001' then hour end)
                     )

Upvotes: 1

alreadythere
alreadythere

Reputation: 1

you can join your table on itself:

select p1.YM
,coalesce(p2.code,'0') as code
,coalesce(p2.hour,'0') as hour
from pod11 As p1
left join pod11 As p2
    on p2.YM = p1.YM
    and p2.code = p1.code
where p1.YM between '201801' and '201804'
    and coalesce(p2.code,'0') in ('0', '001')

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43584

MSSQL:

SELECT 
    tableYM.YM, 
    ISNULL(pod11.code, 0) AS code, 
    ISNULL(SUM(pod11.hour), 0) AS hour 
FROM (
    SELECT DISTINCT YM FROM pod11 WHERE YM >= '201801' AND YM <= '201804'
) tableYM LEFT JOIN pod11 ON tableYM.YM = pod11.YM AND pod11.code = '001'
GROUP BY pod11.code, tableYM.YM
ORDER BY tableYM.YM

demo: http://sqlfiddle.com/#!18/f068b/3/0

MySQL:

SELECT 
    tableYM.YM, 
    IFNULL(pod11.code, 0) AS code, 
    IFNULL(SUM(pod11.hour), 0) AS hour 
FROM (
    SELECT DISTINCT YM FROM pod11 WHERE YM >= '201801' AND YM <= '201804'
) tableYM LEFT JOIN pod11 ON tableYM.YM = pod11.YM AND pod11.code = '001'
GROUP BY pod11.code, tableYM.YM
ORDER BY tableYM.YM

demo: http://sqlfiddle.com/#!9/f068b/18/0

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

One option is to use a union to bring in records for the months which have no matching code records. In my output, there is data for 201712, because you did not specify a reason why it should not appear, and it has no matching code data. If you don't want that record in there, it would be easy enough to remove it in the WHERE clause.

SELECT YM, code, hour
FROM pod11
WHERE YM BETWEEN '201801' AND '201804' AND code = '001'
GROUP BY YM, code, hour
UNION ALL
SELECT DISTINCT t1.YM, 0, 0
FROM pod11 t1
WHERE NOT EXISTS (SELECT 1 FROM pod11 t2 WHERE t1.YM = t2.YM AND t2.code = '001')
ORDER BY YM;

enter image description here

Demo

Upvotes: 0

Rahul Jain
Rahul Jain

Reputation: 1399

SELECT YM, COALESCE(code,0) code, COALESCE(hour,0) hour
(
    SELECT YM,code,hour
    FROM pod11
    WHERE  YM >= '201801' AND YM <= '201804' and code = '001'
    GROUP by YM,code,hour

    RIGHT JOIN

    (SELECT 201801 as YM
    UNION
    SELECT 201802
    UNION
    SELECT 201803
    UNION
    SELECT 201804
    ) as t
    ON pod11. YM = t.YM
)
    ORDER BY YM

You can store the year in different table and query from that or if you know all the years will be there in pod11 table i.e. 201801 to 201804, then you can query from that table with range and distinct

Upvotes: 0

Related Questions