showing a grandtotal of values in a field

good pm. i was thinking is it possible to show the summation or grand total of a selected field in the table and with relation to date:

for example is i want to know the total beer consumption of my hotel every month.

i have here my table on services:

[services_id[pk],
customer_id[fk],
date_in,date_out,room_type,room_number,
extra_ref,
extra_bed,extra_snack,
extra_beer,extra_softdrinks,
extra_pillows,extra_breakfast,
extra_snack_q,
extra_beer_q,
extra_softdrinks_q,
extra_pillows_q,
extra_breakfast_q]

can you give some advice on how can i get 开发者_如何转开发it. thanks in advance:

-renz

SELECT SUM(beer_amount) as monthly_beer_amount
FROM [DATABASE].[TABLE] 
WHERE beer_sold_date BETWEEN '20110201' AND '20110228'

[EXTRA INFO]

Also I believe that the best way to organize this table is to separate out this table into a few other tables. Store customer info in the first table such as

[customer_id, customer_name, date_in, date_out, room_type, room_number]. 
1, Bob, 20110101, 20110110, big, 200
2, Joe, 20110101, 20110110, small, 202
....

And have another table named something like room_items which would have the following,

[id, item_name]
1, BEER
2, BED
3, SNACK
...

And then another table named room_purchases which will have the following,

[customer_id, purchase_id, amount, date....]
1, 1, 10, 20110101
2, 3, 5, 20110101
3, 1, 9, 20110101
....

This would help you to do a join on all three tables and they would be more normalized in this way.

SELECT SUM(t2.amount) as beer_amount
FROM customers as t1 
LEFT JOIN room_purchases as t2 ON t1.customer_id = t2.customer_id
WHERE t2.purchase_id = 1 AND t2.date BETWEEN 20110101 AND 20110131

SELECT DATE_FORMAT(date_in,"%m-%Y") AS month,SUM(extra_beer) AS beers
FROM your_table
GROUP BY month

this will return something like this:

02-2011 | 43
03-2011 | 52

if you want to limit the query depending on the date just add a WHERE constraint before the GROUP BY