List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 26 2005 4:02am
Subject:Re: Crosstab in Mysql
View as plain text  
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
>  
>

Attachment: [text/html]
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
Thread
Crosstab in MysqlAlvaro Cobo26 Apr
  • RE: Crosstab in Mysqlmathias fatene26 Apr
  • Re: Crosstab in MysqlPeter Brawley26 Apr
  • Re: Crosstab in MysqlAlvaro Cobo26 Apr