CODEWITHSUNDEEP
mysqlsql
adams
adams

Reputation: 309

Horizontal total monthly sales for procedures

I am trying to generate total monthly sales for 2021 based on the procedure name by month. Each row must contain procedure name and months names and if any month has any sale, the total of the sale must be output otherwise zero. so far I managed what I am looking for displaying the procedure names and months, however the months with sales showing the total sales of the year,

SELECT 
    tt.procedure_name,
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'January', 1, 0)) AS 'January',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'February', 1, 0)) AS 'February',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'March', 1, 0)) AS 'March',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'April', 1, 0)) AS 'April',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'May', 1, 0)) AS 'May',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'June', 1, 0)) AS 'June',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'July', 1, 0)) AS 'July',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'August', 1, 0)) AS 'August',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'September', 1, 0)) AS 'September',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'October', 1, 0)) AS 'October',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'November', 1, 0)) AS 'November',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'December', 1, 0)) AS 'December'
FROM 
    treatments tt
        LEFT JOIN (
            SELECT 
                plan_date,procedure_name
            FROM 
                treatments
            WHERE 
                YEAR(plan_date) = 2021 
            GROUP BY 
                DATE_FORMAT(plan_date, '%M'),procedure_name
            ORDER BY 
                plan_date ASC,
                procedure_name ASC 
        ) mt ON mt.procedure_name = tt.procedure_name
WHERE 
    YEAR(tt.plan_date) = 2021 
GROUP BY 
    tt.procedure_name 
ORDER BY 
    tt.procedure_name ASC

Wrong Table:

|procedure_name|January|February|March|April|May|June|July|August|September|October|November|December|
|--------------|-------|--------|-----|-----|---|----|----|------|---------|-------|--------|--------|
|Botox         |5      |0       |0    |0    |0  |0   |0   |0     |0        |0      |0       |5       |
|Fillers       |2      |0       |2    |0    |0  |0   |0   |0     |0        |0      |0       |0       |

Correct table should be:

|procedure_name|January|February|March|April|May|June|July|August|September|October|November|December|
|--------------|-------|--------|-----|-----|---|----|----|------|---------|-------|--------|--------|
|Botox         |2      |0       |0    |0    |0  |0   |0   |0     |0        |0      |0       |3       |
|Fillers       |1      |0       |1    |0    |0  |0   |0   |0     |0        |0      |0       |0       |

Upvotes: 0

Views: 61

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Why the self outer join? I suppose you simply want:

SELECT 
    tt.procedure_name,
    SUM(MONTH(tt.plan_date) =  1) AS "January",
    SUM(MONTH(tt.plan_date) =  2) AS "February",
    SUM(MONTH(tt.plan_date) =  3) AS "March",
    SUM(MONTH(tt.plan_date) =  4) AS "April",
    SUM(MONTH(tt.plan_date) =  5) AS "May",
    SUM(MONTH(tt.plan_date) =  6) AS "June",
    SUM(MONTH(tt.plan_date) =  7) AS "July",
    SUM(MONTH(tt.plan_date) =  8) AS "August",
    SUM(MONTH(tt.plan_date) =  9) AS "September",
    SUM(MONTH(tt.plan_date) = 10) AS "October",
    SUM(MONTH(tt.plan_date) = 11) AS "November",
    SUM(MONTH(tt.plan_date) = 12) AS "December"
FROM 
    treatments tt
WHERE 
    YEAR(tt.plan_date) = 2021 
GROUP BY 
    tt.procedure_name 
ORDER BY 
    tt.procedure_name ASC;

If you want to include treatments that have no sale in any month in 2021:

SELECT 
    p.procedure_name,
    SUM(MONTH(tt.plan_date) =  1) AS "January",
    SUM(MONTH(tt.plan_date) =  2) AS "February",
    SUM(MONTH(tt.plan_date) =  3) AS "March",
    SUM(MONTH(tt.plan_date) =  4) AS "April",
    SUM(MONTH(tt.plan_date) =  5) AS "May",
    SUM(MONTH(tt.plan_date) =  6) AS "June",
    SUM(MONTH(tt.plan_date) =  7) AS "July",
    SUM(MONTH(tt.plan_date) =  8) AS "August",
    SUM(MONTH(tt.plan_date) =  9) AS "September",
    SUM(MONTH(tt.plan_date) = 10) AS "October",
    SUM(MONTH(tt.plan_date) = 11) AS "November",
    SUM(MONTH(tt.plan_date) = 12) AS "December"
FROM 
  (SELECT DISTINCT procedure_name FROM treatments) p
LEFT OUTER JOIN
    treatments tt 
      ON tt.procedure_name = p.procedure_name
      AND YEAR(tt.plan_date) = 2021 
GROUP BY 
    p.procedure_name 
ORDER BY 
    p.procedure_name ASC;

Update: You say you have a procedures table. In that case I suggest you use this and join the aggregated data:

SELECT *
FROM procedure p
LEFT JOIN
(
  SELECT 
      procedure_name,
      SUM(MONTH(plan_date) =  1) AS "January",
      SUM(MONTH(plan_date) =  2) AS "February",
      SUM(MONTH(plan_date) =  3) AS "March",
      SUM(MONTH(plan_date) =  4) AS "April",
      SUM(MONTH(plan_date) =  5) AS "May",
      SUM(MONTH(plan_date) =  6) AS "June",
      SUM(MONTH(plan_date) =  7) AS "July",
      SUM(MONTH(plan_date) =  8) AS "August",
      SUM(MONTH(plan_date) =  9) AS "September",
      SUM(MONTH(plan_date) = 10) AS "October",
      SUM(MONTH(plan_date) = 11) AS "November",
      SUM(MONTH(plan_date) = 12) AS "December"
  FROM treatments
  WHERE YEAR(plan_date) = 2021 
  GROUP BY procedure_name
) t ON t.procedure_name = p.procedure_name
ORDER BY p.procedure_name;

Upvotes: 1

Related Questions