Extract names from another table

Question

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?

Answer 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 ;

Answer 2

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