Using: Mysql 4.0
MySqlFront 2.5

I have a table with ifnromation like so:

item sales date
ROM 2004-04-30
INC 2004-03-02

How can I write a query so that the information is grouped and displayed horizontally?

ie:
Itm Jan Feb MAr Apr
ROM 0 0 0 1
INC 0 0 1 0

If anyone can help, it would be grteatly appreciated.

First off this isn't a homework assignment.

I just can't figure out how to make case statements or something along those lines in order to group results into cloumns.

If anyone could simply point me in the right direction, that'll be all I need.


Thanks.

Use a Sum() function with a CASE statement to make the pivot work. Sum up the rows that are in the right month.

-PatP

I will try that, and hopefully post the working code for others to learn from.

Here is the code in order to use Sum and case to group results.

SELECT SUM (CASE when sales_date >= '2003-01-01' and
sales_date <= '2003-01-31' then qty else 0 end)
as 'Jan',
SUM (CASE when sales_date >= '2003-02-01' and
sales_date <= '2003-02-28' then qty else 0 end)
as 'Feb',
FROM sales

Just to avoid loosing lost hair (from pulling it out), I'd tweak your syntax just a smidgeon.This gets you around needing to figure out how many days are in which months (which is just lazy on my part), but it also prevents you from loosing any data entered with a time on the last day of the month too!

-PatP

You're right it makes more sense your way.