Hi,
I need to create sales report that should work like this:
when you select YEAR you get DOMAIN and sales for every month of selected year.
DOMAIN TABLE
site_id domain
1 test.com
2 test-help.com
ITEM TABLE
item_id site_id added_datestamp
1 1 2010-07-15
2 2 2010-06-15
3 2 2010-06-15
4 2 2010-07-15
5 1 2010-03-15
6 2 2010-07-15
7 1 2010-07-15
site_id month_1 month_2 month_3 month_4 month_5 month_6 month_7 month_8 month_9 month_10 month_11 month_12
1 0 0 1 0 0 0 2 0 0 0 0 0
2 0 0 0 0 0 2 2 0 0 0 0 0
What should be logic for this kind of query?
Thanks
SELECT
site_id
, MONTH(added_datestamp) AS monthofyear
, COUNT(*) AS total
FROM item_table
WHERE YEAR(added_datestamp) = $selected_year
GROUP BY
site_id
, MONTH(added_datestamp)
ORDER BY
site_id
, monthofyear
And then you’ll have to loop through the result set and create your table.
Thanks guido2004 it worked:
SELECT si.DOMAIN, uc.SITE_ID, MONTH( uc.ADDED_DATESTAMP ) AS monthofyear, COUNT( uc.ITEM_ID ) AS total
FROM uo_carts AS uc, uo_websites AS si
WHERE YEAR( uc.ADDED_DATESTAMP ) = '2010'
AND uc.SITE_ID = si.SITE_ID
GROUP BY si.DOMAIN, MONTH( uc.ADDED_DATESTAMP )
ORDER BY si.DOMAIN, monthofyear
LIMIT 0 , 30
Regards 
thank for reply… I made mistake, result should give domain instead of site id…
site_id month_1 month_2 month_3 month_4 month_5 month_6 month_7 month_8 month_9 month_10 month_11 month_12
1 0 0 1 0 0 0 2 0 0 0 0 0
2 0 0 0 0 0 2 2 0 0 0 0 0
should be
site_id month_1 month_2 month_3 month_4 month_5 month_6 month_7 month_8 month_9 month_10 month_11 month_12
test.com 0 0 1 0 0 0 2 0 0 0 0 0
test-HELP.com 0 0 0 0 0 2 2 0 0 0 0 0
i.e. I need to join those 2 tables…
Thanks