CODEWITHSUNDEEP
mysqlsql
Felix Lambert
Felix Lambert

Reputation: 21

sql query to get all data?

i have a table 

tid iditem quantity
1   k1      2
1   k2      3
1   k3      4
2   k1      1
2   k7      1
2   k3      1
3   k8      1
3   k2      1

how to get all tid where iditem quantity is >= 3 ? so, iditem k7 and k8 is eliminated. so i want an output which is like this

tid iditem quantity
    1   k1      2
    1   k2      3
    1   k3      4
    2   k1      1
    2   k3      1
    3   k2      1

    SELECT tid, id_item as item , sum(quantity) as tot FROM `detail`
 group by id_item having sum(quantity) >= 5 ORDER BY tid, sum(jumlah) DESC

but the output will be like this

tid iditem quantity
1   k1     2
1   k2     3
2   k3     1

what is a correct query to get an output that i want??

Upvotes: 0

Views: 496

Answers (1)

Siyual
Siyual

Reputation: 16917

You can do this with a sub-query to get all the IDs that match the condition, and pulling directly from the table:

Select  tid, iditem, quantity
From    YourTable
Where   iditem In
(
    Select  iditem
    From    YourTable
    Group By idItem
    Having Sum(quantity) >= 3
)

Upvotes: 1

Related Questions