What you want to do is "pivot your data into a crosstab report". This has
been covered several times so you should be able to find more information
if you search on either "pivot table", "crosstab" or "cross tab".
Here's how to do it for a week's worth of dates, I will let you extend it
to fit your needs. And, before you ask, there is no generic solution to
this issue in MySQL. It is a fairly simple pattern which makes dynamic
solutions fairly easy to script.
SELECT error
, SUM(if(date='2004-07-08',1,0)) as '2004-07-08'
, SUM(if(date='2004-07-09',1,0)) as '2004-07-09'
, SUM(if(date='2004-07-10',1,0)) as '2004-07-10'
, SUM(if(date='2004-07-11',1,0)) as '2004-07-11'
, SUM(if(date='2004-07-12',1,0)) as '2004-07-12'
, SUM(if(date='2004-07-13',1,0)) as '2004-07-13'
, SUM(if(date='2004-07-14',1,0)) as '2004-07-14'
FROM tablename
GROUP BY error;
I know it seems clumsy to need to define every column but it actually
processes rather quickly.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jonas Fischer <[EMAIL PROTECTED]> wrote on 08/03/2004 03:39:39 PM:
> Hello
>
> right now I have a table which looks like this
> (including duplicate entries)
>
> Error Date
> A001 2004-07-08
> A001 2004-07-08
> A222 2004-07-08
> A001 2004-07-09
>
>
> Now I query for the quantity one error occurs during a
> day (select Date, Error,
>
> Count(*) FROM table1 GROUP BY Date and Error). The
> result looks like this:
>
> Date Error Quantity
> 2004-07-08 A001 2
> 2004-07-08 A222 1
> 2004-07-09 A001 1
>
>
> What I would like to have right now is a result which
> looks like this:
>
> Date 2004-07-08 2004-07-08
> Error
>
> A001 1 1
> A222 2 0
>
> As a header I would like to have the dates and than
> display the error on the left side and the quantity of
> how often the error occures beneath under each date.
>
> My problem is, that I do not always have an entry for
> every error per day.
>
> Anybody knows how to do that?
>
> Thank you very much for your help.
>
> Jonas
>
>
>
>
>
>
> ___________________________________________________________
> Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier
> anmelden: http://mail.yahoo.de
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
