Sum of numbers in CTE-CodePudding

For this CTE show counts of working/not working days.

with a(id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
select * from a

I got this result but I changed CTE.

My request:

with a(id, days) as (values (1,0),(1,0),(1,1),(1,1),(1,1),(1,0),(1,0),(2,1),(2,1),(2,1),(2,1),(2,0),(2,0),(2,0))
select id, 'Working' as day_type, sum(days) "COUNT" from a group by shop_id union select id, 'Non-working' as day_type, count(days) - sum(days) "COUNT" from a group by id order by id, day_type

CodePudding user response：

If I understand your question, you want to be able to generate the output from your second query without altering the initial CTE a (which presumably represents an actual table or something).

There will be multiple ways of doing this, but one possibility is something like:

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
, b as (SELECT shop_id, MON   TUE   WED   THUR   FRI   SAT   SUN cnt FROM a)
SELECT shop_id, 'Working', cnt
FROM b
GROUP BY shop_id, cnt
UNION ALL
SELECT shop_id, 'Non-working', 7-cnt
FROM b
GROUP BY shop_id, cnt

Here, the CTE b contains the sum of working days for each shop_id. This is then unioned together with the non-working days (7-working). Using the second CTE isn't necessary, but lets us avoid repeating the bit that adds all the days together. If you prefer, you could do:

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
SELECT shop_id, 'Working', MON   TUE   WED   THUR   FRI   SAT   SUN cnt
FROM a
UNION ALL
SELECT shop_id, 'Non-working', 7-(MON   TUE   WED   THUR   FRI   SAT   SUN)
FROM a

Another options is to actually unpivot the table (convert the columns into rows) and then group by the shop_id and day_type. Something like:

with a(shop_id, MON, TUE, WED, THUR, FRI, SAT, SUN) as (values (1,0,0,1,1,1,0,0),(2,1,1,1,1,0,0,0))
,
b AS (SELECT shop_id, CASE P.w WHEN 0 THEN 'Non-working' ELSE 'Working' END day_type
FROM a, TABLE (VALUES(a.MON),
                  (a.TUE),
                  (a.WED),
                  (a.THUR),
                  (a.FRI),
                  (a.SAT),
                  (a.SUN)) AS P(w))
SELECT shop_id, day_type, count(*) cnt
FROM b
GROUP BY shop_id, day_type

In all cases above, you can use an ORDER BY to get the rows in the order you want them.

Here is a Fiddle of these working.