SQL Count Parole

Asked

Viewed 898 times

0

I have the following table

+----------+----------+
|   data   |    valor |
+----------+----------+
| 16/05/15 |        1 |
| 16/05/15 |        0 |
| 16/05/15 |        1 |
| 16/05/15 |        0 |
| 17/05/15 |        2 |
| 17/05/15 |        1 |
| 17/05/15 |        1 |
| 17/05/15 |        0 |
| 18/05/15 |        2 |
| 18/05/15 |        2 |
| 18/05/15 |        1 |
| 18/05/15 |        0 |
| 19/05/15 |        2 |
| 19/05/15 |        2 |
| 19/05/15 |        2 |
| 19/05/15 |        2 |
+----------+----------+

I need to make an sql that counts the amount of 0, 1 and 2, but I am only able to do with separate sql, how can I make a single select?

SELECT data, COUNT(valor) 
FROM treatment_output 
WHERE valor = 0 
GROUP BY data ORDER BY data
sql

  • Do you want to count the amount of these 3 numbers (value) ? are only these ? is separated by Date ? or goes if one column for each?

  • 1

    Dude, Voce was so close... SELECT data, COUNT(value) FROM treatment_output -WHERE value = 0 GROUP BY data,value ORDER BY data

  • why the "Conditional"?

  • I need value=0, 1 and 2 to be counted and create new columns for each of them.

4 answers

4

You will need to select all records, group by value and use the aggregation function COUNT.

So the COUNT will count the records of each separate group.

SELECT 
    data,
    valor, 
    COUNT(valor) as quantidade
FROM treatment_output
GROUP BY data, valor ORDER BY valor

EDIT: added the field data in the GROUP BY. The question does not clarify that you want grouping by date.

  • 1

    Could you at least comment because my response was bad for me to improve?

  • Below, you just forgot to include the date in the groupby

  • I give up, the question specifically says that he wants an sql that count the quantity of 0, 1 and 2... he does not speak at any time about date...

3

SELECT data, COUNT(valor) 
FROM treatment_output 
--WHERE valor = 0 
GROUP BY data,valor ORDER BY data

0

Look at it this way:

select count(*), valor from treatment_output
group by valor
  • 1

    have a Where lost there in consultation..

  • kkkk. I had copied the clause Where value = 0 from it and did not remove it completely.

0

SELECT QTD,COUNT(*) Ocorrencias
From
(
SELECT data, COUNT(valor) QTD
FROM treatment_output 
WHERE valor = 0
GROUP BY data 
) VIRTUAL
Group by QTD

Browser other questions tagged sql

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