Wednesday, 18 September 2013

Selecting of specific distinct rows based on the column that used GROUP BY in mysql

Selecting of specific distinct rows based on the column that used GROUP BY
in mysql

This are the records of my table:
item_code | qty
001 1
001 1
002 2
002 2
003 2
004 1
005 1
This is my code for SELECTING distinct item_code and total count of qty
based on GROUP BY qty:
select distinct(item_code), count(qty) as qty from imp_inv group by item_code
and this is the result:
item_code | qty
001 2
002 4
003 2
004 1
005 1
What I am struggling for is that how can I select an item_code with a
where clause of qty, this is what I tried:
select distinct(item_code), count(qty) as qty from imp_inv where qty = 2
group by item_code
and this is the result:
item_code | qty
002 2
002 2
003 2
The code above is only selecting the item_code that has the qty of 2, What
I want is to select an item_code based on its total count of the qty, This
is the result of what I want to have:
item_code | qty
001 2
003 2
Hope you all understand my question, Thank you for your help.

No comments:

Post a Comment