CODEWITHSUNDEEP
mysqlsqlphpmyadmingroup-by
Hope Ashcraft
Hope Ashcraft

Reputation: 15

Mysql wrong syntax

An example:

SELECT AVG(cost)
FROM items
WHERE seller_id=6

Lets say I want to find the average cost of items from seller 6 and the average cost of items from seller 10. However I need to do it in one SQL. When I do seller_id = 6 AND 7. It gives me the average cost of 6 and 7. However I want the average of 6 AND the average of 7.

This is the actual question: Our orders have 5 priorities: "1-URGENT", "2-HIGH", "3-MEDIUM", "4-NOT SPEC", "5-LOW". Which type of orders has the highest sum of ‘total price’?

my sql:

SELECT SUM(o_totalprice)
FROM orders
WHERE `o_order-priority`='1-URGENT' AND '2-HIGH' AND "3-MEDIUM" AND "4-NOT SPEC" AND "5-LOW"

Upvotes: 0

Views: 108

Answers (5)

Hanky Panky
Hanky Panky

Reputation: 46900

SELECT seller_id,AVG(cost) FROM items 
WHERE seller_id IN (6,10)
GROUP BY seller_id

And if you want for all the sellers just remove the seller_id IN (6,10) clause

SQL Fiddle For 2 sellers

SQL Fiddle for all sellers

Edit based on your question's edit

SELECT o_order-priority,
SUM(o_totalprice) as total
FROM orders
GROUP BY o_order-priority
ORDER BY total DESC
LIMIT 1

That gives you the o_order-priority that has the maximum sum. And if you remove the limit, that gives you total sum for all the priorities.

Upvotes: 4

Rhythem Aggarwal
Rhythem Aggarwal

Reputation: 356

A bit of theory before i post the code
while using aggregate functions like avg,sum etc you need to understand that average will be calculated on a particular group. say you want to find average marks of girls and boys then the grouping is based on gender. similarly in your case, you want to find average on seller 6 so basically you first need to form groups of sellers based in their seller id.

now when you have formed groups you select the group with seller id 6.
Now your query changes to

SELECT AVG(cost)
FROM items
WHERE seller_id=6       << Selecting the group 
GROUP BY seller_id      << Making groups.

If you want to learn what i did, try running this query

Select AVG(COST),seller_id
From ITEMS
GROUP BY seller_id

This would return the average cost and seller_id of all the sellers.

Hope this helps.

NOTE: While using a aggregate function there always should be a group by clause attached, and also the selected columns must be in group by clause. If you have more columns selected and fewer in group by clause then that would result in a error.

For your second question this query would work

Select MAX(SUM(o_total_price))
FROM ORDERS
GROUP BY o_order-priority

Again same theory, group the columns, then fin the sums in each group and then select the maximum sum among the groups.

Upvotes: -1

S. M. Mohiuddin
S. M. Mohiuddin

Reputation: 388

Please try this.

SELECT seller_id,AVG(cost)
FROM items
WHERE seller_id in (6,10)
group by seller_id

If you need seller wise AVG cost for all seller then try

SELECT seller_id,AVG(cost)
FROM items
group by seller_id

Upvotes: 0

Dmitry Grekov
Dmitry Grekov

Reputation: 688

try this

SELECT seller_id, AVG(cost)
FROM items
WHERE seller_id in (6,7)
GROUP BY seller_id

Upvotes: 0

Raffy Cortez
Raffy Cortez

Reputation: 448

Try to group it using seller_id.

SELECT seller_id, AVG(cost) 
FROM items 
WHERE seller_id in (6,10)
GROUP BY seller_id

Upvotes: 0

Related Questions