How can I do this without nested/sub queries?

[codefighta]

In the db I have this:

ITEM  QTY  DATE
---------------------
1234  2    2004-01-23
1234  3    2004-01-26
1234  1    2004-02-12
1234  5    2004-06-18
4321  8    2004-03-21
4321  2    2004-04-11

How can I get the result below without using sub queries (pre mySQL 4.1)?

ITEM  JAN FEB MAR APR MAY JUN JUL ... DEC
-----------------------------------------
1234   5   1               5
4321           8   2

Thanks
cf

 

[swampBoogie]

select item,
       sum(case when extract(month from date) = 1 then qty end) as jan,
       sum(case when extract(month from date) = 2 then qty end) as feb,
       ...
from t
group by item

 

[cdlvj]

create a temp table of your totals using the group by, and then the select to print the report.

 

[codefighta]

Thanks swampBoogie!

When I went back and tried your solution I discovered that I am pulling data from a FoxPro table for this particular query. Everything else in my project is mySql and I just forgot this. Sorry

I am getting the following error:

    [error_message_prefix] => 
    [mode] => 1
    [level] => 1024
    [code] => -29
    [message] => DB Error: null value violates not-null constraint
    [userinfo] => select imnum, 
          sum(case when extract(month from date) = 1 then qty end) as jan,
          sum(case when extract(month from date) = 2 then qty end) as feb,
          sum(case when extract(month from date) = 3 then qty end) as mar,
          sum(case when extract(month from date) = 4 then qty end) as apr,
          sum(case when extract(month from date) = 5 then qty end) as may,
          sum(case when extract(month from date) = 6 then qty end) as jun,
          sum(case when extract(month from date) = 7 then qty end) as jul,
          sum(case when extract(month from date) = 8 then qty end) as aug,
          sum(case when extract(month from date) = 9 then qty end) as sep,
          sum(case when extract(month from date) = 10 then qty end) as oct,
          sum(case when extract(month from date) = 11 then qty end) as nov,
          sum(case when extract(month from date) = 12 then qty end) as dec
          from itsar404.dbf WHERE cnum='23651' AND imnum<>'' group by imnum [nativecode=S1000 [Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).]
    [backtrace] => Array

Even though this is not mySql, do you have any suggestions for what might be wrong?

Thanks!
Sorry for not paying more attention before I posted.
cf

 

[swampBoogie]

FoxPro? Not a clue about what SQL it supports.