GroupBy Query mysql

Question

I have a table with values like the following

Name     DatePurchased     QuantityPurchased

A      2/3/2012             1
A      2/4/2012             1 
A      2/5/2012             2
B      2/2/2012             1
B      2/3/2012             2

I want to output the following

Name        DatePurchased         QuantityPurchased

A            2/3/2012               1
A            2/4/2012               2      // as I have purchased 2 upto this date
A            2/5/2012               4      // as I have purchased 4 upto this date
B            2/2/2012               1
B            2/3/2012               3

My query

SELECT Name,  `DatePurchased` , SUM(QuantityPurchased) 
FROM table1
GROUP BY DatePurchased

does not do the math right. I know whats wrong but can't figure out the solution.

Thanks

Try replacing SUM() with COUNT(). This may or may not work depending on what results you get currently; please post the output your current query generates. — Bojangles
– Bojangles, Commented Mar 8, 2012 at 0:22

mathematical.coffee · Accepted Answer · 2012-03-08 00:27:01Z

1

Try:

SELECT t1.Name, t1.DatePurchased, SUM(t2.QuantityPurchased)
FROM table1 t1
LEFT JOIN table1 t2
ON t1.Name=t2.Name AND t1.DatePurchased >= t2.DatePurchased
GROUP BY Name, t1.DatePurchased

This joins table1 to itself within Name and such that t1s date is always at least t2s date, and sums up t2s QuantityPurchased (for each name,date in t1).

(Try performing the same query with SELECT *, and without the SUM and GROUP BY to see the joined table. Then the SUM and GROUP BY will become clear).

answered Mar 8, 2012 at 0:27

mathematical.coffee

57.1k15 gold badges160 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kibbee · Accepted Answer · 2012-03-08 00:28:08Z

1

Rewriting the answer, misread it the first time. Now I understand that you want a running total. I believe something like this should work.

Select B.Name,B.DatePurchased,SUM(B.QuantityPurchased)
FROM Table1 AS A
INNER JOIN Table1 AS B
ON A.Name=B.Name AND B.DatePurchased <= A.DatePurchased
GROUP BY B.Name,B.DatePurchased

edited Mar 8, 2012 at 0:28

answered Mar 8, 2012 at 0:22

Kibbee

66.2k28 gold badges145 silver badges184 bronze badges

1 Comment

mathematical.coffee Over a year ago

That's not what OP wants - they want a cumulative sum up to each date.

Muhammad Saifuddin · Accepted Answer · 2012-03-08 00:39:34Z

0

hope it helps!

SELECT Name,  `DatePurchased` , SUM(QuantityPurchased) 
FROM table1
GROUP BY Name, DatePurchased
ORDER BY Name, DatePurchased

answered Mar 8, 2012 at 0:39

Muhammad Saifuddin

1,29411 silver badges29 bronze badges

Collectives™ on Stack Overflow

GroupBy Query mysql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related