Re: Crosstab in Mysql

[Alvaro Cobo]

You guys are the best!!!. 

I have spent hours trying to solve this problema and Peter´s answer was the
solution. I´ll write a post with the whole explanation of the problem. 

Thanks and best regards, 

Alvaro. 

PD: Mathias, I´ll send the tables with data and the solution. Thanks!. 
  ----- Original Message ----- 
  From: Peter Brawley 
  To: Alvaro Cobo 
  Cc: mysql@stripped 
  Sent: Monday, April 25, 2005 11:02 PM
  Subject: Re: Crosstab in Mysql


  Alvaro,

  I suspect the error message refers to the subquery missing a non-aggregate column on
which to GROUP BY.

  I may misunderstand your query--I'm not clear why you can't just write ...

  SELECT 
    f.PK_partic, 
    f.FK_IS, 
    f.OB_familia, 
    Sum( IF( insumo_or = "Animal1", cantidad_or, 0 )) AS Animal1, 
    Sum( IF( insumo_or = "Animal2", cantidad_or, 0 )) AS Animal2, 
    Sum( IF( insumo_or = "Animal3", cantidad_or, 0 )) AS Animal3, 
    Sum( IF( insumo_or = "Animal4", cantidad_or, 0 )) AS Animal4, 
    Sum( IF( insumo_or = "Animal5", cantidad_or, 0 )) AS Animal5, 
    Sum( IF( insumo_or = "Animal6", cantidad_or, 0 )) AS Animal6
  FROM tbl_ISv2CRfamilia AS f
  INNER JOIN tbl_ISv2CROriginal AS a ON f.PK_partic = a.FK_partic
  GROUP BY f.FK_partic

  HTH

  PB

  -----

  Alvaro Cobo wrote: 
Hi guys: 

I am quite new in SQL and I need to build a crosstab based in two tables using Mysql and
PHP, but it is becoming quite dificult. 

I've got the next query, but it keeps giving the next error: 

"#1241 - Operand should contain 1 column(s)" 

/*GENERAL EXPLANATION OF THE QUERY

I work in a project to give animals to farmers: I have two tables: 

tbl_ISv2CRfamilia with the families which are going to receive animals. 
    (PK_partic, int(11), Autonumbering ID (PK); 
    FK_IS; varchar(255); Foreign key which conects to the project table
    nombre_partic, varchar(255), Name of the family
    OB_familia, varchar(255), community of the family)

tbl_ISv2CROriginal with the animales they actually have received. 
    (FK_partic, int(11), Foreign key which conects to the family ID
    insumo_or, varchar(255), Animal given
    cantidad_or, int(11), number of animals given of this specie)

And I need to have a table like this: 

Family,     animal1,     animal2,     animal3,     ...,     animaln
John Smith     3    4    0    1    3    ...     0
*/


The query and subquiery is as follows. 

SELECT tbl_ISv2CRfamilia.PK_partic, 
tbl_ISv2CRfamilia.FK_IS, 
tbl_ISv2CRfamilia.OB_familia, 
(SELECT Sum(
IF (
insumo_or = "Animal1", cantidad_or, 0
) ) AS "Animal1", Sum(
IF (
insumo_or = "Animal2", cantidad_or, 0
) ) AS "Animal2", Sum(
IF (
insumo_or = "Animal3", cantidad_or, 0
) ) AS "Animal3", Sum(
IF (
insumo_or = "Animal4", cantidad_or, 0
) ) AS "Animal4", Sum(
IF (
insumo_or = "Animal5", cantidad_or, 0
) ) AS "Animal5", Sum(
IF (
insumo_or = "Animal6", cantidad_or, 0
) ) AS "Animal6"
FROM tbl_ISv2CROriginal
GROUP BY FK_partic
)
FROM tbl_ISv2CRfamilia
INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic =
tbl_ISv2CROriginal.FK_partic
GROUP BY FK_partic

What is wrong with that? I have tried everything, and no solution. 

Thanks in advance. 

Alvaro

  ----------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  

------------------------------------------------------------------------------


  No virus found in this outgoing message.
  Checked by AVG Anti-Virus.
  Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005