Mysql selecting with sum() and group by from multiple tables

Question

TABLE 1

---------------------------------------
| id | item | MU | quantity | file_id |
---------------------------------------
| 1  |item1 | oz | 3.5      | 003     |
| 2  |item2 | kg | 1.1      | 001     |
| 3  |item1 | oz | 0.2      | 001     |
| 3  |item1 | kg | 3        | 001     |

TABLE 2

 ----------------------------
 | id | date      | file_id |
 ----------------------------
 | 1  |timestamp1 | 001 |
 | 2  |timestamp2 | 002     |
 | 3  |timestamp3 | 003     |

What i'm trying to do is to select the sum of quantity for each group of items that have the same MU. I manage to do that with this query.

$query = "SELECT item,SUM(quantity) from table1 GROUP BY item,mu";

with a great result, exactly what i wanted
----------------------------------
| id | item | MU | SUM(quantity) |
----------------------------------
| 1  |item1 | oz | 3.7           |
| 2  |item2 | kg | 1.1           |
| 3  |item1 | kg | 3             |

But now, how do i get only the rows that their file_id date is BETWEEN two timestamps ?

What's the PRIMARY KEY on each of these tables?

Strawberry
– Strawberry

2013-05-19 10:09:14 +00:00
Commented May 19, 2013 at 10:09 — Strawberry
– Strawberry, Commented May 19, 2013 at 10:09

John Woo · Accepted Answer · 2013-05-19 10:03:32Z

3

You can join both table using INNER JOIN. The result of the query will only show items from Table1 which matches between the two dates, otherwise it is not included in the result list.

SELECT  MIN(a.iD) ID, a.item, a.MU,
        SUM(a.quantity) totalQuantity
FROM    table1 a
        INNER JOIN table2 b
            ON a.file_ID = b.file_ID 
WHERE   b.date BETWEEN startDateHere AND endDateHere 
GROUP   BY a.item, a.MU

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

answered May 19, 2013 at 10:03

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

John Woo Over a year ago

i think you want this. Use INNER JOIN.

John Woo Over a year ago

one more thing, MIN(a.iD) ID is optional.

Fabio · Accepted Answer · 2013-05-19 10:03:46Z

2

You can manage to achieve this using LEFT JOIN and joining the two tables in a single query, simply replace initialdate and finaldate with your current timestamp

$query = "SELECT a.id, a.item, a.mu, SUM(a.quantity) as total_quantity from table1 a left join table2 b ON a.file_id = b.file_id WHERE b.date BETWEEN initialdate AND finaldate GROUP BY a.item,a.mu";

answered May 19, 2013 at 10:03

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

4 Comments

user348078 Over a year ago

great, i knew that i should read more about mysql JOIN :D, it's not very clear in my head :) , thanks

user348078 Over a year ago

so if i want from all these returned by your query just those items (from table 1) that their id is present in a third table?

Fabio Over a year ago

@user348078 i would suggest to post a new question with your code and the problem you find in compiling it

John Woo Over a year ago

there is no need to post a new question. the simpliest answer with the followup question by the OP is to use INNER JOIN not LEFT JOIN. just as simple as that.

Collectives™ on Stack Overflow

Mysql selecting with sum() and group by from multiple tables

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related