1

I have data in two tables. One has order details, the other has product details. The order_details table references the product table via the product_id column. The product table has columns product_id and product_name.

I count sales per product ID like this:

SELECT product_id, COUNT(*)
FROM order_details
GROUP BY product_id
ORDER BY COUNT(*) DESC

and get output like this:

product_id  
123         2500
421         1700
111         1040

Now I want to have the names from the product table instead of the IDs:

apples  2500
banana  1700
pear    1040

How can I do that?

0

2 Answers 2

1

The (possibly) most efficient way, first group, then join:

SELECT
    product_name, c
FROM
    (
      SELECT product_id, COUNT(*) AS c
      FROM order_details
      GROUP BY product_id
    ) AS counts
    /* The trick is now to JOIN this virtual table with your product table */
    JOIN product p ON p.product_id = counts.product_id
ORDER BY 
    c DESC ;

You can check that it works in this SQL Fiddle (scaled by a 10 factor).

This SQL is very close to standard, so it should work not only with MySQL, but with most SQL databases (I've tested it with MySQL, PostgreSQL and SQLite).


Most contemporary databases will probably execute the following query with the same efficiency, provided that NOT NULL and REFERENCES are in place (and you type a bit less, and probably make it easier to understand):

SELECT 
    p.product_name, COUNT(*) AS c
FROM 
    order_details od
    JOIN product p ON p.product_id = od.product_id
GROUP BY 
    p.product_id
ORDER BY 
    count(*) DESC ;

Or, without using aliases:

SELECT 
    product.product_name, COUNT(*) AS c
FROM 
    order_details
    JOIN product ON product.product_id = order_details.product_id
GROUP BY 
    product.product_id
ORDER BY 
    count(*) DESC ;
2
  • Thank you very much ...I used the second version...it works but I don't understand exactly why :) select product_name ...but where is written from table "product"? :)
    – antony
    Jan 28, 2017 at 17:21
  • The product table is written in the FROM clause, where you do two things: add aliases (p is a shortcut or an alias for product and od is an alias for order_details) so that you write a bit less, and JOIN both tables, which the condition that the product_id must be the same on both.
    – joanolo
    Jan 28, 2017 at 17:24
0

Since you didn't provided product table information,i just simulated a table as like below and please find the query

create table product_tab(product_id number(3),product_name varchar2(10))

create table order_details(product_id number(3),order_amount number(5))

SELECT a.product_name,b.product_id, COUNT(*)
FROM product_tab a,order_details b
where a.product_id=b.product_id
GROUP BY b.product_id,a.product_name
ORDER BY COUNT(*) DESC

output:

apples  123 11
banana  421 8
pear    111 3
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.