On Wed, 13 Oct 2004 20:25:48 -0700, Chris <[EMAIL PROTECTED]> wrote: >> create table test_table >> (d date not null, >> name char(32) not null, >> loc integer not null, >> type integer not null, >> amount integer, >> primary key (d, name, loc, type)); snip snip > This is how I would do it: > > SELECT > d as day, > SUM(amount) as total, > SUM(IF(1=loc,amount,0)) as loc1_total, > SUM(IF(2=loc,amount,0)) as loc2_total > FROM test_table > GROUP BY d > ; > > I'm not sure this is the best way to go, as that SUM(IF()) functionality > seems a bit unclean to me, but it works. >
Thanks, that works great! Now, on a somewhat related note, I'm trying to get the data from mysql in a ready to use format with no post-processing required... I'm graphing the data from this query using dates on the x axis. The input to my graph module (GD::Graph) requires a constant-length list. So if any days in my selection range have no data, I need to fill the space with an empy value. For example, say my date range is '2004-10-01' to '2004-10-05' and the query returns: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-04, 250 I have to do some date manipulation in perl afterward to check for missing values... If I could get a query that returned: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-03, NULL 2004-10-04, 250 2004-10-05, NULL That would be so much nicer in some cases. I'm thinking it would be sort of like the output from a LEFT JOIN if I had a table containing just a bunch of sequential dates...but I don't...and the dates can actually be arbitrary, so it would have to be a pretty big table, with no real data in it. Does anyone know of some way to fake this "date table"? (again, I'm using mysql 4.0.16) ...stuck on these LEFT JOINS lately for some reason ;-) Thanks, -partap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
