[MYSQL] overzichts tabel

[htca]

Ik heb in een mysql database een tabel tickets, van deze tabel wil ik een overzicht hebben waarin per maand is op gesplitst de status,:

code:

Month       Closed   Open Unasgn Suspen Analyz Reprod  Fixed  Total
Dec-2004:      68     37     10     23      5      3      1    147
Nov-2004:      90     12      2      3     13      2      2    124
Oct-2004:      88     11      0      1      3      2      1    106
Sep-2004:      90     14      0      2      5      1      1    113
Aug-2004:      61      5      2      0      0      0      0     68
Jul-2004:     131     22      1      0      4      1      1    160
Jun-2004:      84      7      0      0      1      3      0     95
May-2004:      54      1      0      0      1      0      0     56
Apr-2004:      70      8      1      0      0      0      0     79
Mar-2004:      76     11      0      1      0      0      0     88
Feb-2004:      86      5      3      0      0      0      0     94
Jan-2004:      69      8      1      2      0      0      0     80
Older   :     442    244     26     28      9      0      3    752
Total   :    1409    385     46     60     41     12      9   1962

Momenteel doe ik dat door een sqlstatement te gebruiken en vervolgens via een C-programma'tje te halen om dit vervolgens uit te spugen. Dit wordt aangestuurd via een cronjob.

Echter ik zou graag een sql statement hebben om dit in een keer te doen, voor de volledigheid, de tickets tabel bevat "status" en "create_date". Het statement wat ik nu heb geeft me alleen per maand een overzichtje en dan ben ik nog niet van mijn C-programma af:

code:

SELECT status, COUNT(*) As number FROM tickets 
WHERE create_date<UNIX_TIMESTAMP('2004-11-1 00:00:00') AND create_date>UNIX_TIMESTAMP('2004-10-1 00:00:00') 
GROUP BY status ORDER BY status Desc;

+------------+--------+
| status     | number |
+------------+--------+
| Suspended  |      1 |
| Reproduced |      2 |
| Open       |     11 |
| Fixed      |      1 |
| Closed     |     88 |
| Analyzed   |      3 |
+------------+--------+

[MacWebber]

Volgens mij zou je dit kunnen doen middels een query in drie delen (die je dan met een tweetal unions weer aan elkaar plakt.

Het eerste deel (de andere twee kun je dan vast zelf verzinnen) is:

code:

select
    extract(from_unixtime(create_date), '%Y %M') month,
    sum(if(status = 'Closed', 1, 0)) Closed,
    sum(if(status = 'Open', 1, 0)) Open,
    sum(if(status = 'Suspended', 1, 0)) Suspended,
    sum(if(status = 'Analyzed', 1, 0)) Analyzed,
    sum(if(status = 'Reproduced', 1, 0)) Reproduced,
    sum(if(status = 'Fixed', 1, 0)) Fixed,
    count(*) Total
from
    tickets
where
    create_date >= unix_timestamp(date_sub(curdate(), interval 1 year)) 
group by
    extract(from_unixtime(create_date), '%Y %M')
order by
    extract(from_unixtime(create_date), '%Y %M')

Toegegeven, ik heb het niet kunnen testen, heb die tabel niet en ben te lui om hem na te maken

Maar dit geeft volgens mij het eerste deel van je opvraging, de maanden van het laatste jaar, met uitsplitsing van de bonnen per status. Nadeel is uiteraard dat als er een status bijkomt, je dit ook aan moet passen. De truuk is dus middels het IF() statement in de SELECT een aantal kolommen te maken die de verschillende statussen tellen.

[MMUilwijk]

Wat betreft je maand probleem, SQL kent gewoon een MONTH functie. En verder moet je volgens mij met een paar effectieve SUM functies en GROUP aggregaten en heel eind komen
(kan nu alleen even niet testen )

[htca]

MacWebber schreef op maandag 27 december 2004 @ 17:43:
Volgens mij zou je dit kunnen doen middels een query in drie delen (die je dan met een tweetal unions weer aan elkaar plakt.

Het eerste deel (de andere twee kun je dan vast zelf verzinnen) is:

code:

select
    extract(from_unixtime(create_date), '%Y %M') month,
    sum(if(status = 'Closed', 1, 0)) Closed,
    sum(if(status = 'Open', 1, 0)) Open,
    sum(if(status = 'Suspended', 1, 0)) Suspended,
    sum(if(status = 'Analyzed', 1, 0)) Analyzed,
    sum(if(status = 'Reproduced', 1, 0)) Reproduced,
    sum(if(status = 'Fixed', 1, 0)) Fixed,
    count(*) Total
from
    tickets
where
    create_date >= unix_timestamp(date_sub(curdate(), interval 1 year)) 
group by
    extract(from_unixtime(create_date), '%Y %M')
order by
    extract(from_unixtime(create_date), '%Y %M')

Toegegeven, ik heb het niet kunnen testen, heb die tabel niet en ben te lui om hem na te maken

Maar dit geeft volgens mij het eerste deel van je opvraging, de maanden van het laatste jaar, met uitsplitsing van de bonnen per status. Nadeel is uiteraard dat als er een status bijkomt, je dit ook aan moet passen. De truuk is dus middels het IF() statement in de SELECT een aantal kolommen te maken die de verschillende statussen tellen.

Ik heb dit geprobeerd en dit kwam eruit:

code:

ERROR 1064 at line 1: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'from_unixtime(create_date), '%Y %M') month,     sum(if(status =

from_unixtime(create_date), '%Y %M') month is niet goed, ben nog aan het zoeken...

[ Voor 9% gewijzigd door htca op 28-12-2004 14:14 ]

[MacWebber]

Volgens mij mag

code:

extract(from_unixtime(create_date), '%Y %M')

omgezet in:

code:

from_unixtime(create_date, '%Y %M')

[ Voor 214% gewijzigd door MacWebber op 28-12-2004 14:41 ]

[htca]

[quote]MacWebber schreef op dinsdag 28 december 2004 @ 14:36:
Volgens mij mag

code:

extract(from_unixtime(create_date), '%Y %M')

het werkt dank je wel!

[ Voor 41% gewijzigd door htca op 28-12-2004 14:49 ]