SQL syntax "Transform" in MySQL?

Hi all.

I have this table in the DB MySQL:

key | date
--------------
AAA 2006-04-03
AAA 2006-04-03
AAA 2006-04-04
AAA 2006-04-04
AAA 2006-04-05
AAA 2006-04-05
AAA 2006-04-05
BBB 2006-04-03
BBB 2006-04-03
BBB 2006-04-04
BBB 2006-04-04
BBB 2006-04-05
BBB 2006-04-05
BBB 2006-04-05
CCC 2006-04-03
CCC 2006-04-03
CCC 2006-04-04
CCC 2006-04-04
CCC 2006-04-05
CCC 2006-04-05
CCC 2006-04-05

I need this:

 key 2006-04-03 2006-04-04 2006-04-05
 ------------------------------------
 AAA      2          2          3
 BBB      2          2          3
 CCC      2          2          3

Whit database MS Access I use TRANSFORM syntax but this syntax not working whit database MySQL…

Can you help me?

Regards
Viki

use DISTINCT and COUNT, look at the MySQL site for the correct sintax and usage

Thanks x you reply… :slight_smile:

I write this query… its’ right:


SELECT
key_1,
  COUNT( IF( date = 2006-04-03, date, NULL )) AS 2006-04-03,
  COUNT( IF( date = 2006-04-04, date, NULL )) AS 2006-04-04

(...)

FROM myTable
GROUP BY key_1

Key is reserverd word in DB MySQL… :slight_smile:

Any better typs ?

Some time ago i used transform in mysql this is the sintax:


"TRANSFORM Count(IND_PROPIETARIOS.PRI_APELLIDO) AS CuentaDePRI_APELLIDO" & _
        " SELECT IND_PROPIETARIOS.IND as Inicial FROM (DEPARTAMENTO INNER JOIN MAE_FICHAS ON DEPARTAMENTO.ID_Depto = MAE_FICHAS.ID_Depto) INNER JOIN IND_PROPIETARIOS ON (MAE_FICHAS.IND = IND_PROPIETARIOS.IND) AND (MAE_FICHAS.imagen = IND_PROPIETARIOS.imagen) AND (MAE_FICHAS.ID_TIPO_FICHA = IND_PROPIETARIOS.ID_TIPO_FICHA)" & _
        " WHERE (((IND_PROPIETARIOS.PRI_APELLIDO)<>'')) AND ((IND_PROPIETARIOS.ID_RELACION) In ('04','05','07'))" & _
        " GROUP BY IND_PROPIETARIOS.IND" & _
        " PIVOT DEPARTAMENTO.DEPARTAMENTO"

This sintax works for me, but the response time was long… maybe my sintax was not perfect but it works…jeje

I hope you can use this example…

gderas, that will not work in mysql, sorry

viki, you should use CASE (which is standard sql), not IF (which will work only in mysql)

thanks x your reply.

You can tell me ?

SELECT
key_1,
  COUNT( CASE( date = 2006-04-03, date, NULL )) AS 2006-04-03,
  COUNT( CASE( date = 2006-04-04, date, NULL )) AS 2006-04-04

(...)

FROM myTable
GROUP BY key_1

you need to look up the CASE syntax in da manual – you forget the WHEN, THEN, ELSE, and END keywords

also, mysql dates need to be properly delimited

:slight_smile:

Please one example for my case… thanks…

okay, one example

the way to specify a date in mysql is to enclose it in single quotes

so instead of date = 2006-04-03 you should write date = ‘2006-04-03’

note that date is enclosed in backticks (because DATE is a reserved word), while ‘2006-04-03’ is enclosed in single quotes because it’s a string

OK thanks… I understand:

SELECT
key_1,
  COUNT( CASE( `date` = '2006-04-03', `date`, NULL )) AS XXX,
  COUNT( CASE( `date` = '2006-04-04', `date`, NULL )) AS ZZZ

(...)

FROM myTable
GROUP BY key_1

But I dont understand this:

you forget the WHEN, THEN, ELSE, and END keywords

please, learn to look stuff up in the manual

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case

see the examples there? compare them to what you wrote

then look again at what i said: “you forget the WHEN, THEN, ELSE, and END keywords”

Thanks I understand.

It’s right this query ? ( working… )


SELECT
key_1,
  COUNT( case when `date` = '2006-04-03' then `date` else NULL end ) AS XXX,
  COUNT( case when `date` = '2006-04-04' then `date` else NULL end ) AS ZZZ

(...)

FROM myTable
GROUP BY key_

yes :slight_smile: :slight_smile:

You are the KING in this forum MySQL :wink: