change rows to columns and count

Question

how to calculate count based on rows?

SOURCE TABLE each employee can take 2 days off

Employee-----First_Day_Off-----Second_Day_Off
1------------10/21/2009--------12/6/2009
2------------09/3/2009--------12/6/2009
3------------09/3/2009--------NULL
4
5
.
.
.

Now i need a table that shows the dates and number of people taking off on that day

Date---------First_Day_Off-------Second_Day_Off
10/21/2009---1-------------------0
12/06/2009---1--------------------1
09/3/2009----2--------------------0

Any ideas?

score 2 · Answer 1 · answered Nov 08 '10 at 19:24

Oracle 9i+, using Subquery Factoring (WITH):

WITH sample AS (
   SELECT a.employee,
          a.first_day_off AS day_off,
          1 AS day_number
     FROM YOUR_TABLE a
    WHERE a.first_day_off IS NOT NULL
   UNION ALL
   SELECT b.employee,
          b.second_day_off,
          2 AS day_number
     FROM YOUR_TABLE b
    WHERE b.second_day_off IS NOT NULL)
  SELECT s.day_off AS date,
         SUM(CASE WHEN s.day_number = 1 THEN 1 ELSE 0 END) AS first_day_off,
         SUM(CASE WHEN s.day_number = 2 THEN 1 ELSE 0 END) AS second_day_off
    FROM sample s
GROUP BY s.day_off

Non Subquery Version

  SELECT s.day_off AS date,
         SUM(CASE WHEN s.day_number = 1 THEN 1 ELSE 0 END) AS first_day_off,
         SUM(CASE WHEN s.day_number = 2 THEN 1 ELSE 0 END) AS second_day_off
    FROM (SELECT a.employee,
                 a.first_day_off AS day_off,
                 1 AS day_number
            FROM YOUR_TABLE a
           WHERE a.first_day_off IS NOT NULL
          UNION ALL
          SELECT b.employee,
                 b.second_day_off,
                 2 AS day_number
            FROM YOUR_TABLE b
           WHERE b.second_day_off IS NOT NULL) s
GROUP BY s.day_off

@mahen: Don't forget to upvote answers you find helpful by clicking the arrow on top of the number to the left of the answer, and accept answers when you're satisfied -- [for more information on accepting answers, see this link](http://meta.stackexchange.com/questions/5234) — OMG Ponies, Nov 08 '10 at 20:27
If I'm not mistaken, there isn't a difference between both, right ? Although I prefer using the `with` keyword. — Sathyajith Bhat, Nov 09 '10 at 05:13

score 1 · Answer 2 · edited May 23 '17 at 10:26

It is a bit awkward to handle these queries, since you have days off stored in different columns. A better layout would be to have something like

EMPLOYEE_ID    DAY_OFF

Then you would have multiple rows if an employee took multiple days off

EMPLOYEE_ID    DAY_OFF
1              10/21/2009
1              12/6/2009
2              09/3/2009
2              12/6/2009
3              09/3/2009
...

In that case, you could find out how many days off each person took by using the following query:

SELECT EMPLOYEE_ID, COUNT(*) AS NUM_DAYS_OFF FROM DAYS_OFF_TABLE GROUP BY EMPLOYEE_ID

And the number of people who took days off on each date like this:

SELECT DAY_OFF, COUNT(*) AS NUM_PEOPLE FROM DAYS_OFF_TABLE GROUP BY DAY_OFF

But I digress...

You can try to use an SQL CASE statement to help with this:

SELECT Employee, CASE
    WHEN First_Day_Off is NULL AND Second_Day_Off is NULL THEN 0
    WHEN First_Day_Off is NOT NULL AND Second_Day_Off is NULL THEN 1
    WHEN First_Day_Off is NULL AND Second_Day_Off is NOT NULL THEN 1
    ELSE 2
    END AS NUM_DAYS_OFF
FROM DAYS_OFF_TABLE

(note that you may need to change around the syntax slightly depending on your database.

Getting dates and number of people who took off on that day might be more complicated.

I don't know if this would work, but you can try it:

SELECT
    Date_Off,
    COUNT(*) AS Num_People
FROM
    (SELECT
        First_Day_Off, COUNT(*) AS Num_People FROM DAYS_OFF_TABLE WHERE First_Day_Off IS NOT NULL GROUP BY First_Day_Off
    UNION
    SELECT Second_Day_Off, COUNT(*) AS Num_People FROM DAYS_OFF_TABLE WHERE Second_Day_Off IS NOT NULL GROUP BY Second_Day_Off)
GROUP BY
    Num_People

ya... but i have them as my source columns...i need to find the stats ... how many people on a particular day took 1st day off and 2nd say off... — mahen, Nov 08 '10 at 19:21

change rows to columns and count

2 Answers2

Oracle 9i+, using Subquery Factoring (WITH):

Non Subquery Version