0
I have a client field on it I need to make a Count of how many customers were registered during the years 2016, 2017 and 2018
i was making 3 select, being that in each one I was making a Where dataCadastro >= 2016-01-01 and dataCadastro <= 2016-12-31
only it’s very slow, because it runs 3 select
it is possible to select only by grouping this information by year?
3
Follows:
SELECT COUNT(*) AS QTD_CLIENTES_CADASTRADOS, EXTRACT(YEAR FROM DATACADASTRO) AS ANO
FROM TABELA
WHERE EXTRACT(YEAR FROM DATACADASTRO) IN (2016, 2017, 2018)
GROUP BY EXTRACT(YEAR FROM DATACADASTRO);
Explanation:
When using the function EXTRACT (YEAR FROM DATE)you will extract the day/month/year from a determining field timestamp of your database, with this function, you can make conditional, use in the order by, add into SELECT among other features.
I used the in to prevent the addition of three and (which would refer to each year), thus avoiding the SQL great without need.
Edited
There’s another way to do this SQL avoiding so many EXTRACTS:
SELECT COUNT(S.QTD) AS CLIENTES_CADASTRADOS, S.ANO
FROM (SELECT 1 AS QTD, EXTRACT(YEAR FROM DATACADASTRO) AS ANO
FROM TABELA) AS S
WHERE S.ANO IN (2016, 2017, 2018);
GROUP BY S.ANO;
1
If dates are in date or datetime fields, you can use YEAR
YEAR ( date ) - Returns an integer representing the year of the specified date.
SELECT COUNT(coluna), YEAR(dataCadastro) FROM tabela GROUP BY YEAR(dataCadastro);
Example:
Table
Upshot SELECT COUNT(id), YEAR(dataCadastro) FROM tabela2 GROUP BY YEAR(dataCadastro);
If the field
dataCadastrofor varchar type, first you have to convert to date format using the function STR_TO_DATE, then you can use the YEAR function.YEAR(STR_TO_DATE(dataCadastro, "%Y-%m-%d"))
0
If YEAR is in date format then you can use Mysql SUBSTRING
SELECT Count(*) as total , SUBSTRING(year, 1.4)
FROMcliente
GROUP by SUBSTRING(year, 1,4)
Browser other questions tagged mysql sql select
You are not signed in. Login or sign up in order to post.
Hello I tried to do the search this way and this returning an error message, could anyone help me? SQL: select datapedido, sum(total) total from pedido Where status>0 group by substr(datapedido,0,7) order by datapedido desc Error: select datapedido, sum(total) total from pedido Where status>0 group by substr(datapedido,0,7) order by datapedido desc LIMIT 0, 25 Mysql messages : Documentation #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'server.pedido.datapedido' which is not functionally dependent on Columns in GROUP BY clause; this
– Fabio Branco da Silva