Separate WHERE IN conditions for each column

Question

My product table looks like this:

id_product   |   product_count  |  product_name  
1            |   10             |  name1
2            |   20             |  name2
3            |   20             |  name3
4            |   52             |  name4
5            |   30             |  name5
6            |   50             |  name6

And I want to do a query which result will be:

 product_group_name   |   product_group_name | product_group_name |...more groups
 30(sum)              |   72(sum)            | 50(sum)            |...more values

where group will be id_product, like (1,2 OR 3,4 OR 1,4,5) and group name will be alias.

Here is sql code to make a single column:

SELECT 
    cast(SUM(product.product_count) as float) as group_name
FROM    
    product
WHERE 
    product.id_product in (608,913,528,529,920,406,407,408,912,919,918,917,924,926,925)

and result of this query is:

| group_name |
| 200        |

Is it possible to add more columns with group names? It is important for them to be columns, not rows.

if i got u rigth, yes you can..just use coma(',') after column,.. `SELECT cast(SUM( product.product_count) as float) as group_name,sum(column2) as group_name2` — J. Zend, Jul 04 '16 at 09:21
I think you will have to do the following things 1). Do a join with the product_group table 2). Do a "Group by" using the group_id and perform a sum() operation 3). Once you get results in the above step, you can transpose the results by using any of the methods described here - http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — ArunGeorge, Jul 04 '16 at 09:30

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

I think that PIVOT might be what you're looking for. Check this question too.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 04 '16 at 10:15

maialithar

3,065
5
27
44

score 0 · Answer 2 · answered Jul 04 '16 at 11:52

If I understand you correctly, one way to do it is by conditional aggregation:

SELECT 
    cast(SUM(
        CASE WHEN product.id_product in (608,913,528, ... ,924,926,925)
        THEN product.product_count ELSE NULL END) as float) as group_name1
    ,cast(SUM(
        CASE WHEN product.id_product in (1,2,3)
        THEN product.product_count ELSE NULL END) as float) as group_name2
    ,cast(SUM(
        CASE WHEN product.id_product in (4,5)
        THEN product.product_count ELSE NULL END) as float) as group_name3
FROM
    product
;

Separate WHERE IN conditions for each column

2 Answers2