SQL case problem

Question

SELECT
    CASE
        WHEN OfferSum.DayOfWeek = 'Monday' THEN 'Mon'
        WHEN OfferSum.DayOfWeek = 'Tuesday' THEN 'Tues'
        WHEN OfferSum.DayOfWeek = 'Wednesday' THEN 'Wed'
        WHEN OfferSum.DayOfWeek = 'Thursday' THEN 'Thurs'
        WHEN OfferSum.DayOfWeek = 'Friday' THEN 'Fri'
        WHEN OfferSum.DayOfWeek = 'Saturday' THEN 'Sat'
        WHEN OfferSum.DayOfWeek = 'Sunday' THEN 'Sun'
    END [DayOfWeek]
   ,OfferSum.TotalCount [TotalCount]
FROM (Select COUNT(*) [TotalCount] FROM Offer) AS OfferSum

expected output

Mon 8

Tues 0

Wed 1

Thurs 1

Fri 0

Sat 2

Sun 0

But the days without data are not shown in the table. How can I do it?

score 1 · Accepted Answer · answered Oct 04 '21 at 14:18

SQL cannot return data that does not exist... if there is no monday in your data, SQL won't "guess" that you are expecting something for monday... (The case does not what you are expecting here).

If you want SQL to show you data for each day, then you need a lookup table that would contain all days and then, you could simply do a left join on your other tables.

Here's a quick example that you could use as a starting point:

create table #MyData (Day_week varchar(10), totalcount int);
insert into #MyData values ('monday',1),('monday',5),('monday',3),
('friday',3),('friday',8),
('saturday',12),('saturday',2),('saturday',1),('saturday',1);
-- This is similar to your example, counting the # of totalcount for each day. This does not show missing date
SELECT
    CASE
        WHEN Day_week = 'Monday' THEN 'Mon'
        WHEN Day_week= 'Tuesday' THEN 'Tues'
        WHEN Day_week = 'Wednesday' THEN 'Wed'
        WHEN Day_week = 'Thursday' THEN 'Thurs'
        WHEN Day_week = 'Friday' THEN 'Fri'
        WHEN Day_week = 'Saturday' THEN 'Sat'
        WHEN Day_week = 'Sunday' THEN 'Sun'
    END [DayOfWeek]
   ,count(TotalCount) [TotalCount]
from #MyData
group by CASE
        WHEN Day_week = 'Monday' THEN 'Mon'
        WHEN Day_week= 'Tuesday' THEN 'Tues'
        WHEN Day_week = 'Wednesday' THEN 'Wed'
        WHEN Day_week = 'Thursday' THEN 'Thurs'
        WHEN Day_week = 'Friday' THEN 'Fri'
        WHEN Day_week = 'Saturday' THEN 'Sat'
        WHEN Day_week = 'Sunday' THEN 'Sun'
    END;
-- This is the lookup table to help you accomplish what you want
create table #lookup (ID int, Day_week varchar(10), Short_Day varchar(5));
insert into #lookup values (1,'Monday','Mon'),(2,'Tuesday','Tues'),(3,'Wednesday','Wed'),(4,'Thursday','Thurs'),(5,'Friday','Fri'),(6,'Saturday','Sat'),(7,'Sunday','Sun');
select  L.Short_Day, count(d.totalCount) "Count of totalCount"
from #lookup L 
left join #MyData D on L.Day_week=D.Day_week
group by L.ID, L.Short_Day
order by L.ID;
-- Cleaning up
drop table #MyData;
drop table #lookup;

Note that I added an "ID" column in my lookup table, this is only to be able to show the result order by day (you may not want the output to be "Friday, monday, saturday..."

SQL case problem

1 Answers1