CODEWITHSUNDEEP
mysqlsqlsubquery
Gavin
Gavin

Reputation: 2365

MySQL Query within a query on same table

I have a table like:

Status | Quantity | PeriodDate
------------------------------
100    | 2        | 2013-07-02
100    | 3        | 2013-07-02
100    | 5        | 2013-07-02
100    | 3        | 2013-07-03
100    | 1        | 2013-07-03
100    | 0        | 2013-07-04
100    | 0        | 2013-07-04
100    | 5        | 2013-07-04
100    | 6        | 2013-07-05
100    | 4        | 2013-07-06
100    | 8        | 2013-07-06

And I need to select data such that it displays the count of Quantity for each distinct dates in a range. I would have thought I could do something to the tune of:

SELECT DISTINCT PeriodDate, SUM(Quantity) as TotalQuantity where PeriodDate BETWEEN '2013-07-02' AND '2013-07-04'

But this returns one row with the entire sum of all quantities.

I'm after something like:

TotalQuantity | PeriodDate
--------------------------
10            | 2013-07-02
4             | 2013-07-03
5             | 2013-07-04
6             | 2013-07-05

How do I achieve this?

Upvotes: 1

Views: 48

Answers (2)

tomahaug
tomahaug

Reputation: 1476

Distinct works on both PeriodDate and TotalQuantity. Instead of using DISTINCT, you need to use GROUP BY.

SELECT PeriodDate, SUM(Quantity) as TotalQuantity 
FROM ???
WHERE PeriodDate BETWEEN '2013-07-02' AND '2013-07-04'
GROUP BY PeriodDate

Explaination: Distinct is used to eliminate duplicate rows in a resultset.

Further explaination: http://www.mysqltutorial.org/mysql-distinct.aspx

Upvotes: 3

juergen d
juergen d

Reputation: 204746

SELECT PeriodDate, 
       SUM(Quantity) as TotalQuantity 
from your_table
where PeriodDate BETWEEN '2013-07-02' AND '2013-07-05'
group by PeriodDate

Upvotes: 4

Related Questions