MySQL: Group columns without column with null

Question

I'm trying to group this "imaginary" table (the result of several joins and if's)

Table, how it is:

ProductID PriceForOneYear PriceForTwoYears PriceForThreeYears PriceForFourYears PriceForFiveYears
1         1.00            NULL             NULL               NULL             NULL
1         NULL            1.50             NULL               NULL             NULL
1         NULL            NULL             2.00               NULL             NULL
1         NULL            NULL             NULL               2.50             NULL
1         NULL            NULL             NULL               NULL             3.00
2         5.00            NULL             NULL               NULL             NULL
2         NULL            5.50             NULL               NULL             NULL
2         NULL            NULL             6.00               NULL             NULL
2         NULL            NULL             NULL               6.50             NULL
2         NULL            NULL             NULL               NULL             7.00

Table, how it should be:

ProductID PriceForOneYear PriceForTwoYears PriceForThreeYears PriceForFourYears PriceForFiveYears
1         1.00            1.50             2.00               2.50             3.00
2         5.00            5.50             6.00               6.50             7.00

The NULL's should fall out.

Any idea?

I tried "GROUP BY .." and GROUP_CONCATE(DISTINCT ..) so far.

Query that creates my "imaginary table":

SELECT
`tblproducts`.`id` AS `productid`,
IF(`tblproductconfigoptionssub`.`optionname` = '1', `tblpricing`.`monthly`, NULL) AS `priceforoneyear`,
IF(`tblproductconfigoptionssub`.`optionname` = '2', ROUND(`tblpricing`.`monthly` / 2, 2), NULL) AS `pricefortwoyears`,
IF(`tblproductconfigoptionssub`.`optionname` = '3', ROUND(`tblpricing`.`monthly` / 3, 2), NULL) AS `priceforthreeyears`,
IF(`tblproductconfigoptionssub`.`optionname` = '4', ROUND(`tblpricing`.`monthly` / 4, 2), NULL) AS `priceforfouryears`,
IF(`tblproductconfigoptionssub`.`optionname` = '5', ROUND(`tblpricing`.`monthly` / 5, 2), NULL) AS `priceforfiveyears`
FROM `tblproducts` INNER JOIN `tblproductconfigoptionssub` ON `tblproducts`.`id` = `tblproductconfigoptionssub`.`configid` INNER JOIN `tblpricing` ON `tblproductconfigoptionssub`.`id` = `tblpricing`.`relid` WHERE `tblproducts`.`gid` = '1' AND `tblpricing`.`type` = 'configoptions';

Gordon Linoff · Accepted Answer · 2014-02-18 19:15:45Z

Change your query to an aggregation query and use max() on the columns:

SELECT `tblproducts`.`id` AS `productid`,
       max(IF(`tblproductconfigoptionssub`.`optionname` = '1', `tblpricing`.`monthly`, NULL)) AS `priceforoneyear`,
       max(IF(`tblproductconfigoptionssub`.`optionname` = '2', ROUND(`tblpricing`.`monthly` / 2, 2), NULL)) AS `pricefortwoyears`,
       max(IF(`tblproductconfigoptionssub`.`optionname` = '3', ROUND(`tblpricing`.`monthly` / 3, 2), NULL)) AS `priceforthreeyears`,
       max(IF(`tblproductconfigoptionssub`.`optionname` = '4', ROUND(`tblpricing`.`monthly` / 4, 2), NULL)) AS `priceforfouryears`,
       max(IF(`tblproductconfigoptionssub`.`optionname` = '5', ROUND(`tblpricing`.`monthly` / 5, 2), NULL)) AS `priceforfiveyears`
FROM `tblproducts` INNER JOIN
     `tblproductconfigoptionssub`
     ON `tblproducts`.`id` = `tblproductconfigoptionssub`.`configid` INNER JOIN
     `tblpricing`
     ON `tblproductconfigoptionssub`.`id` = `tblpricing`.`relid`
WHERE `tblproducts`.`gid` = '1' AND `tblpricing`.`type` = 'configoptions'
GROUP BY `tblproducts`.`id`;

Collectives™ on Stack Overflow

MySQL: Group columns without column with null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related