Sum Count() in mysql

Asked

Viewed 562 times

1

I have this query that returns me the amount of equal dates:

SELECT `Data`, COUNT(`Data`) AS 'Quantidade' FROM sua_tabela GROUP BY `Data`;

| Data         |
| 2016-06-09   |
| 2016-06-09   |
| 2016-06-09   |
| 2016-06-08   |
| 2016-06-08   |
| 2016-06-05   |
| 2016-06-01   |

Returns like this:

| Data       | Quantidade |
| 2016-06-09 |     3      |
| 2016-06-08 |     2      |
| 2016-06-05 |     1      |
| 2016-06-01 |     1      |

I would then like to add the Quantity returned for this month

I tried this way:

SELECT Data_reg, SUM(COUNT(Data_reg)) AS 'Soma_total'
FROM Monit 
WHERE Data_reg like ('2016-06%')
GROUP BY `Data_reg`;

But returned the following error:

SQL Error (1111): Invalid use of group Function

mysql sql query

  • You want to count only the same dates, or only count ?

  • In my table there are several other dates, but I would like to add only the month I studied in the example

  • You’re returning the same error

  • sorry, I took the date in the wrong place: SELECT SUM(COUNT(Data_reg)) AS 'Soma_total' FROM Monit WHERE Data_reg like ('2016-06%') group by data_reg;

  • The same error persists :(

  • SELECT sum(count(data)) qt
 FROM (SELECT '03/01/2018' data FROM DUAL
 UNION ALL
 SELECT '01/01/2018' data FROM DUAL
 UNION ALL
 SELECT '01/01/2018' data FROM DUAL
 UNION ALL
 SELECT '02/01/2018' data FROM DUAL
 UNION ALL
 SELECT '02/01/2018' data FROM DUAL)
 
 GROUP BY data look at this code I made here, which is exactly the same as yours, runs normally, is sure that the error of group by? (will not run in mysql, as the virtual table created in it is part of oracle functions)

Show 1 more comment

1 answer

2


You don’t need to add the COUNT. Just make a COUNT normal that will return the total of LIKE:

SELECT Data_reg, COUNT(Data_reg) AS Soma_total FROM Monit WHERE Data_reg like '2016-06%'

In your case, the above query will return 7, which is exactly the sum of the values in "Quantity" of your initial query (3 + 2 + 1 + 1 = 7):

| Data       | Quantidade |
| 2016-06-09 |     3      |
| 2016-06-08 |     2      |
| 2016-06-05 |     1      |
| 2016-06-01 |     1      |

Browser other questions tagged mysql sql query

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