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