Re: Crosstab in Mysql
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 34013... 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
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 34013... 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Crosstab in Mysql
Hi, What do you obtain with : Select FK_partic, 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 ? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Alvaro Cobo [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 04:38 To: mysql@lists.mysql.com Subject: Crosstab in Mysql 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 34013... 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
