0
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?

2
  • 2
    Your example query is invalid and would throw an error because your subquery for OfferSum doesn't return a column called DayOfWeek. Please fix your example query.
    – J.D.
    Oct 4 at 13:05
  • If the link above doesn't answer your question, please add a minimal reproducible example to your question via the edit link.
    – Hannah Vernon
    Oct 4 at 13:49
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.