Align queries and pass your results as alias for SOMA

Asked

Viewed 62 times

6

So I’m running two queries that return me the amount X of items per month over a year.

The first consultation gives me the following result

inserir a descrição da imagem aqui

The 'no column name' would be the months, so I have no data in August, November and December.

The second consultation, give me the following

inserir a descrição da imagem aqui

only August and September has data.

What I’m trying to figure out is how to add the two tables together to get the 100% value, so I define each% (corrective x preventive). In my research, I suppose I have to nest the queries, but I just can’t reach the logic to approach the desired result.

this is my question so far

    select month(workOrderDate), COUNT(*) as preventivas from WorkOrder WHERE
    workOrderDescription = 'preventiva'
    AND YEAR(workOrderDate) = 2018
    AND lastUpdateData IS NOT NULL
    AND WorkType = '02'
    group BY MONTH(workOrderDate)
    ORDER BY MONTH(workOrderDate)

    select MONTH(workOrderDate), COUNT(*) AS corretivas from WorkOrder WHERE
    YEAR(workOrderDate) = 2018
    AND workOrderDescription = 'CORRETIVA' 
    AND lastUpdateData IS NOT NULL
    GROUP BY MONTH(workOrderDate)
    ORDER BY MONTH(workOrderDate)

The expected result would be

inserir a descrição da imagem aqui

Could someone help me with this ?

sql
  • 2

    https://stackoverflow.com/questions/20320631/how-to-pivot-a-table-in-mysql-using-case-statements see if this helps...

  • 1

    @Motta has nothing to do with my doubt, but thank you for trying

1 answer

2


You can use a CASE (as suggested by Motta) and use the result of this to make the calculations you want:

SELECT x.month,
       x.total AS 'total (100%)',
       (x.preventiva * 100) / x.total AS 'preventivas(%)',
       (x.corretivas * 100) / x.total AS 'corretivas(%)'
  FROM (
    SELECT MONTH(workOrderDate) AS month,
           COUNT(CASE WHEN workOrderDescription = 'preventiva' AND WorkType = '02' THEN 1 END) AS preventivas,
           COUNT(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1 END) AS corretivas,
           COUNT(CASE WHEN (workOrderDescription = 'preventiva' AND WorkType = '02') OR workOrderDescription = 'CORRETIVA' THEN 1 END) AS total
      FROM WorkOrder
     WHERE YEAR(workOrderDate) = 2018
       AND lastUpdateData IS NOT NULL
     GROUP BY MONTH(workOrderDate)
     ORDER BY MONTH(workOrderDate)
  ) x

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.