select Count within select

Asked

Viewed 343 times

1

With the query below this returning the total values of each month, however I need to count the quantities of times each item has in each month. 

SELECT instancia,
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 04) AS abril,
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 05) AS maio,
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 06) AS junho
from urt

imagem

mysql phpmyadmin

  • "Each month" will always be April, May and June or will be every month?

2 answers

4


You don’t need to make multiple selections for this, just make the selection of all the data, group by instance and add the records by month:

SELECT
    instancia,
    SUM(CASE WHEN MONTH(ura_data) = 1 THEN 1 ELSE 0 END) as "Jan",
    SUM(CASE WHEN MONTH(ura_data) = 2 THEN 1 ELSE 0 END) as "Fev",
    SUM(CASE WHEN MONTH(ura_data) = 3 THEN 1 ELSE 0 END) as "Mar"
FROM urt
GROUP BY instancia;

See working on DB-Fiddle

Since in Mysql the boolean value is interpreted as 1 or 0, we can directly add the result of the comparison, without the case when:

SELECT
    instancia,
    SUM(MONTH(ura_data) = 1) as "Jan",
    SUM(MONTH(ura_data) = 2) as "Fev",
    SUM(MONTH(ura_data) = 3) as "Mar"
FROM urt
GROUP BY instancia;

Simplifying a bit the reading of SQL.

  • 2

    This solution is more efficient than using subselects, for a larger volume of data the difference in runtime will become noticeable.

  • 1

    Very good, Anderson boy

  • 1

    @Sorack Master in SQL since I was born xD

  • Show!!! very good

2

References the most external SELECT instance in subselects:

SELECT instancia, 
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 04 AND a.instancia = instancia) AS abril, 
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 05 AND a.instancia = instancia) AS maio, 
    (SELECT COUNT(instancia) FROM urt WHERE MONTH(ura_data) = 06 AND a.instancia = instancia) AS junho 
FROM urt a

  • Got ball show!! thank you very much, just one more thing, has to sort the column of June from bigger to smaller???

  • Yes, use: ORDER BY 4 ASC.

Browser other questions tagged mysql phpmyadmin

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