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@lists.mysql.com
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
