Adapting two queries

Question

Hello again community.

I recently received help with the solution to a problem that consisted of List grouped data by month including months in which there are no results

Now I have the same scenario but with the difference that there is a condition to evaluate. I need to adapt this query

select
    date_format(a.date_created, '%b') month,
    month(a.date_created) pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from
    activities a
        inner join
    employees e on a.employee_id = e.id
where
    e.id = 8
group by 1, 2 order by pivot desc;

To this query

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN activities a
    ON s.m = month(date_created)
group by 1, 2 order by pivot desc;

I do not know how in the context of the solution that worked in the previous question add the condition

I have tested the following but the months in which there are no results are not listed

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN activities a
  ON s.m = month(date_created)
inner join employees e on a.employee_id = e.id
group by 1, 2 order by pivot desc;

I share the following sqlfiddle that includes the relationship between employee and activity

I am expecting for the user with id 2 the results should be as follows

Again, thank you very much.

what should the output look like? one row per month? – Vamsi Prabhala Dec 01 '17 at 21:59 — Vamsi Prabhala, Dec 01 '17 at 21:59
I just updated the question to show the expected output – Mario Dec 01 '17 at 22:29 — Mario, Dec 01 '17 at 22:29

score 0 · Answer 1 · answered Dec 01 '17 at 22:01

I think this version does what you want:

select s.name as month, s.m as pivot,
       sum(case when a.state = 'created' then 1 else 0 end) created,
       sum(case when a.state = 'notified' then 1 else 0 end) notified,
       sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
       sum(case when a.state = 'approved' then 1 else 0 end) approved,
       sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
       sum(case when a.state = 'attended' then 1 else 0 end) attended,
       sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
       count(a.id) as total
from (SELECT  1 m, 'Jan' AS name UNION ALL
      SELECT 2, 'Feb'  UNION ALL
      SELECT 3, 'Mar' UNION ALL
      SELECT 4, 'Apr' UNION ALL
      SELECT 5, 'May' UNION ALL
      SELECT 6, 'Jun' UNION ALL
      SELECT 7, 'Jul' UNION ALL
      SELECT 8, 'Aug' UNION ALL
      SELECT 9, 'Sep' UNION ALL
      SELECT 10, 'Oct' UNION ALL
      SELECT 11, 'Nov' UNION ALL
      SELECT 12, 'Dec'
     ) s LEFT JOIN activities a
     ON s.m = month(date_created) left join
     employees e
     on a.employee_id = e.id AND e.id = 2
group by 1, 2
order by pivot desc;

Here is the SQL Fiddle.

The major change is moving the condition on e.id to the ON clause and changing the second join to a LEFT JOIN. In addition, I changed the UNION to UNION ALL, to eliminate the overhead of removing duplicates.

Thanks for answering. List every month but the results listed are not correct. The employee with id 2 has two activities, both in December, one with created status and the other with notified. What do you think causes this result? — Mario, Dec 01 '17 at 22:18

score 0 · Accepted Answer · answered Dec 02 '17 at 00:04

You just had the joins muddled. For the join between activities and employees to be considered as INNER join it has to be enclosed in brackets. Let's know whether it works!

Here is the solution

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN ( activities a INNER JOIN employees e 
                                 ON a.employee_id = e.id AND e.id = 2)
        ON (s.m = month(a.date_created))
group by 1, 2 order by pivot desc;

Adapting two queries

2 Answers2