CODEWITHSUNDEEP
mysqlsql
Ryan
Ryan

Reputation: 989

MySQL :- Adding where clause to this query

I have 2 queries but I confused where to add where clause.

select supplier_id,count(*) c, party_name, SUM(po_value) po_value
from purchase_order join supplier s on s.id=supplier_id
group by supplier_id
having c>1 ORDER BY c DESC LIMIT 5

Second query

select warehouse_id, warehouse_name, SUM(available_quantity) available_quantity
from stock join warehouse s on s.id=warehouse_id 
group by warehouse_id

In both the queries finally I have to fetch based upon a id like where org_id='152'

Upvotes: 1

Views: 187

Answers (2)

e4c5
e4c5

Reputation: 53754

See the edited query below:

select supplier_id,count(*) c, party_name, SUM(po_value) po_value
from purchase_order join supplier s on s.id=supplier_id

    WHERE purchase_order.org_id='152

group by supplier_id
having c>1 ORDER BY c DESC LIMIT 5

This compares against an org_id in purchase_order use s.org_id if you want to do the looking in the other table.

For the second one

select warehouse_id, warehouse_name, SUM(available_quantity) available_quantity
from stock join warehouse s on s.id=warehouse_id 

    WHERE org_id='152


group by warehouse_id

(note that whitespace is collapsible). For full details see the manual section on select: http://dev.mysql.com/doc/refman/5.7/en/select.html

Upvotes: 1

Mark Townsend
Mark Townsend

Reputation: 181

Add it just before the GROUP BY:

    SELECT supplier_id,count(*) c, party_name, SUM(po_value) po_value
    FROM purchase_order A JOIN supplier s ON s.id=supplier_id
    GROUP BY supplier_id
    WHERE A.org_id='152'
    HAVING c>1 ORDER BY c DESC LIMIT 5

and

    SELECT warehouse_id, warehouse_name, SUM(available_quantity) available_quantity
    FROM stock G JOIN warehouse s ON s.id=warehouse_id 
    WHERE G.org_id='152'
    GROUP BY warehouse_id

Also, from a readability perspective, I would recommend using capitals to mark out the standard SQL terms :)

The question you have asked is also answered quite nicely by the first example on this page:

http://www.w3schools.com/sql/sql_groupby.asp

EDIT: Updated to remove ambiguity on where clause.

Upvotes: 2

Related Questions