CODEWITHSUNDEEP
sqlsql-server
Pelicer
Pelicer

Reputation: 1584

SQL Query to Return SUMS and Count Ordered by Date

I have the two following tables:

Table 1 Table 2

What I want to do is to have a query that returns a SUM of PIT_VALORTOTAL, PIT_VOLUME and a count of PED_IDPESSOA per date. What I have so far is:

SELECT SUM(PIT_VALORTOTAL) AS VALORTOTAL, SUM(PIT_VOLUME) AS VOLUME, COUNT(DISTINCT PED_IDPESSOA) AS PESSOA FROM PEDIDOS_ITENS INNER JOIN PEDIDOS ON PIT_IDPEDIDO = PED_ID;

And it returns the sums and the count correctly, but I don't have a clue on how to get these seperatly per dates. So what I have is this:

VALORTOTAL       | VOLUME             | PESSOA  |
49783.2000000    |       679780.360000|   11    |

And what I want is something like:

| DATE      | VALORTOTAL    | VOLUME     | PESSOA  |
| 2017-09-03| 1012,00       |       1209 |   12    |
| 2017-09-03| 2012,00       |       1450 |   10    |
| 2017-09-03| 3016,00       |       2500 |   20    | 
| 2017-09-03| 3016,00       |       3200 |   5     |
| 2017-09-03| 2016,00       |       4000 |   9     |

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You just need group by:

SELECT PED_DATA, SUM(PIT_VALORTOTAL) AS VALORTOTAL, SUM(PIT_VOLUME) AS VOLUME,
       COUNT(DISTINCT PED_IDPESSOA) AS PESSOA
FROM PEDIDOS_ITENS pi INNER JOIN
     PEDIDOS p
     ON PIT_IDPEDIDO = PED_ID
GROUP BY PED_DATA
ORDER BY PED_DATA

Upvotes: 2

Related Questions