intriguing select statement issue

[ethorn10]

ok, i've thought about this enough. i haven't gotten very far with it, so i'll spell it out for you guys and see how fast somebody can get a star.

i want to grab totals for multiple months (april, may, june, july, august) using one select statement with two tables. so something like

select sum(tbl_a.price) from tbl_a, tbl_b where tbl_b.registered=1 and tbl_b.id_field=tbl_a.id_field and tbl_b.date_applied between...

now i need multiple between statements for every month or what? obviously i'm lost and need a little help here.

 

[Deleted]

What does your table layout look like?

http://unixjunky.com

 

[ethorn10]

tbl_a
---------
item_id
price


tbl_b
---------
person_id
registered
item_id
date_applied

basically want i want is a breakdown of monthly totals. so sum up the price from table a for all of the people in table b who paid for it between 4/1 and 4/30 or any month for that matter, multiple months.

 

[ericbrunson]

I think youd have to find a function to extract the month from the date and group by it.

Something like:

...
group by date_format( date_applied, "%m" )

 

[dalchri]

How about:

SELECT
   Month(tbl_b.date_applied) AS `Month`,
   Year(tbl_b.date_applied) AS `Year`,
   SUM(tbl_a.price) AS Total
FROM tbl_a, tbl_b
WHERE
   tbl_b.registered=1 And 
   tbl_b.id_field = tbl_a.id_field And 
   tbl_b.date_applied between...
GROUP BY 
   Month(tbl_b.date_applied),
   Year(tbl_b.date_applied)

It is probably important to include the year in your grouping unless you can guarantee that the between clause does not span years, otherwise, April 2004 will get added to April 2003 and so on.

Also, this query will not return months for which there are not data. For example, if there are no records in April, there will be no total for April, even if your selection criteria includes April.