Help with simple SQL query

uraknai · #1 (**permalink**) 07-18-07, 05:20

Hi,

I'm making a simple online booking system using mysql and PHP where people can book out equipment.

There is a limited number of bookable items (10 camcorders, 20 laptops etc). When someone books an item, some of the data stored is the item id (which references the equipment table) and the number booked.
After a simple query for the equipemnt booked an a certain day at a certain time, the table looks like this:

Code:

item_id        number_booked
1                      4
4                      10
4                      5
2                      6
1                      6
4                      8

What I need is a table that shows DISTINCT item_id's and the SUM of the number booked for each of these items. So the resulting table would be:

Code:

item_id          total
1                   12
4                   23
2                   6

Hope I've explained it clearly. Could someone give me a hand?

Cheers

mike_bike_kite · #2 (**permalink**) 07-18-07, 06:43

select item_id, sum( number_booker )
from your_table
where ...
group by item_id

The where clause would contain the bit that limits things to a certain day and time etc. The important new bits are the sum() and the group by.

Mike

uraknai · #3 (**permalink**) 07-18-07, 07:49

Quote:

Originally Posted by mike_bike_kite

select item_id, sum( number_booker )
from your_table
where ...
group by item_id

The where clause would contain the bit that limits things to a certain day and time etc. The important new bits are the sum() and the group by.

Mike

Thats great. Works like a charm.

Cheers

Internet Services

Web Development

Digital Marketing

Consumer Tech