[Resolved] Help with a query - a sub select?

fowkswe

Hello,

Im trying to construct a query and am having a hard time wrapping my head around the concept of what I want to do. It seems like the term sub-select would best describe it, but im not sure if that is the correct termonology. Anyway, let me try to explain my situation.

I have a tracking table that has an auto_increment'ing ID, a date field and several columns indicating clicks.

so a short example table would be:

ID | date | click1 | click2 |

I would like to construct a query that gives me click totals for a particular date range. So i would want a row returned for each date that totaled all click 1's and click 2's.

Take for example the following date sets:

ID | date | click1 | click2 |

1 | 7/3/2003 | 3 | 5

2 | 7/3/2003 | 2 | 8

3 | 7/3/2003 | 9 | 6

4 | 7/4/2003 | 3 | 5

5 | 7/4/2003 | 1 | 5

So i would like to be able to get the total clicks on the 3rd and the 4th of july - regardless of the ID.

I can get the total clicks for all dates, with

SELECT SUM(click1) as click1, SUM (click2) as click2 FROM <table> WHERE date>=20030703 AND date <= 20030704

But I cant figure out how to get the sums of each date individually. I would prefer to do this in 1 query if possible and not have to issue a query for each date in the range.

TIA!

Sxooter

What you probably need to do it make a "pivot" table that has all the dates in it, and then left join against that.

create table pivot (dt date);
insert into pivot values ('1-march-2003');
insert into pivot values ('2-march-2003');
...
insert into pivot values ('31-march-2003');

select (your formulae here) from some_table left join pivot on (table.date=pivot.dt) group by ... etc...

fowkswe

Sxooter,

Thanks! Your suggestion worked (almost). It turned out I needed to do a right join on the date table and not a left join on to the tracking table!

Billy

Sxooter

Oops. Yeah, that was the quick and dirty answer. I'm usually a little better than that. Glad it got you where you needed to go though...

Don't forget to mark the thread resolved...