CODEWITHSUNDEEP
mysqlsql
alexanoid
alexanoid

Reputation: 25770

retrieve all rows from the table grouped by condition

I have the following mysql table: 

products

with a fileds:

id, product_group_id, internal_product_id, version, platform_id, name

1   12                12                   1        30           Megacalculator              
2   12                12                   2        30           Megacalculator            
3   16                17                   1        30           Calculator                    
4   16                17                   2        30           Calculator
5   16                18                   0.1      40           Calculator Linux
6   20                19                   2.1      30           Converter Windows
7   20                20                   2.1      40           Converter Linux
8   30                24                   0.1      30           Editor

I need to retrieve all rows from this table grouped by 'product_group_id' but with a different 'internal_product_id' inside of the group. Also, the rows count in each group must be equal to some special number(the value must be supplied into the query as an external parameter)

For example:

external parameter = 2, result:

product_group_id    
16                  
20

external parameter = 1, result:

product_group_id    
12                  
30

Please help me with this sql query.

Upvotes: 0

Views: 47

Answers (2)

echo_Me
echo_Me

Reputation: 37233

Try that:

for parameter = 2

    select `product_group_id` from products
    group by `product_group_id`
    having count(distinct `internal_product_id`) = 2

for parameter = 1

    select `product_group_id` from products 
    group by `product_group_id`
    having count(distinct `internal_product_id`) = 1

DEMO HERE

Upvotes: 1

ktm5124
ktm5124

Reputation: 12123

You can do this:

select product_group_id from products 
group by product_group_id;
having count(internal_product_id) = 2;

Or you can get the information in a table:

select product_group_id, count(internal_product_id) from products 
group by product_group_id;

Upvotes: 0

Related Questions