List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 8:10am
Subject:Re: Crosstabel
View as plain text  
On Mon, 1999-09-27 23:54:44 +0200, Eric Zwart wrote:
> I have a querry running Access like below , Although I want it to work in 
> MySQL with PHP what is the right query for this for making this crosstable?
> 
> "TRANSFORM Sum(Wedstrijd.Punten) AS SumOfPunten
> SELECT Wedstrijd.Klasse, Wedstrijd.WedstrijdNr, Sum(Wedstrijd.Punten) AS 
> [Total Of Punten] FROM Wedstrijd
> GROUP BY Wedstrijd.Klasse, Wedstrijd.WedstrijdNr PIVOT Wedstrijd.Datum";

The TRANSFORM/PIVOT syntax doesn't exist in ANSI SQL and is maybe
unique to Microsoft Access.

The get the same result with MySQL, you have to do build up a quite
complicated query.

First, you've to check out how many and which values the pivot field
Wedstrijd.Datum does have, as you want to create a result column for
each of these.

Let's assume, Wedstrijd.Datum only takes the values '1999-09-27' and
'1999-09-28'.

The query will look like this:

SELECT Klasse
     , WedstrijdNr
     , ...someterm1... AS 1999_09_27
     , ...someterm2... AS 1999_09_28
FROM   Wedstrijd
GROUP BY Klasse
       , WedstrijdNr

Note: I'm using underscores in the column aliases, because otherwise
they wouldn't be legal alias names.

BTW, it's not possible (without a ridiculous complicated query) to get
the total sum Sum(Wedstrijd.Punten) at the same time, so just do that
in a seperate query.  Now back to the main query ...

In ...someterm1... we want to sum-up the field Punten, but only for
rows with Datum='1999-09-27'.  Rows with other date values are to be
ignored, and this can be accomplised if we sum-up numerical 0 (which
doesn't change the sum) or NULL (which is ignored) for such rows:

   SUM( IF(Datum='1999-09-27', Punten, NULL) )

Analogous for ...someterm2...

So the final query is:

SELECT Klasse
     , WedstrijdNr
     , SUM( IF(Datum='1999-09-27', Punten, NULL) ) AS 1999_09_27
     , SUM( IF(Datum='1999-09-28', Punten, NULL) ) AS 1999_09_28
FROM   Wedstrijd
GROUP BY Klasse
       , WedstrijdNr

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
CrosstabelEric Zwart27 Sep
  • Re: CrosstabelMartin Ramsch28 Sep