From: Peter Brawley Date: April 26 2005 4:02am Subject: Re: Crosstab in Mysql List-Archive: http://lists.mysql.com/mysql/183117 Message-Id: <426DBD52.2000209@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426DBD524BA1=======" --=======AVGMAIL-426DBD524BA1======= Content-Type: multipart/alternative; boundary=------------000505000401080603010200 --------------000505000401080603010200 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > --------------000505000401080603010200 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------000505000401080603010200-- --=======AVGMAIL-426DBD524BA1======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-426DBD524BA1=======--