SQL query to find the total number of books sold for each day by merging 2 tables?

Question

I'm trying to find total books sold (sum of quantities) for each product_id and txn_day. I have 2 tables, transactions and catalog as below:

Table 1: transaction

market_id, txn_day, customer_id, product_id, quantity
1,2019-03-01,1,B0002,1
1,2019-03-01,2,B0003,1
1,2019-03-01,1,B0001,1
3,2019-03-01,3,B0001,1
3,2019-03-01,4,B0002,1
4,2019-03-01,1,B0002,1
4,2019-03-01,5,B0001,1
4,2019-03-01,6,B0001,1

Table 2: catalog

market_id, product_id, title_name
1,B0001, Harry Potter 1
1,B0002, Harry Potter 2
1,B0003, Harry Potter 3
3,B0001, Harry Potter 1
3,B0002, Harry Potter 2
3,B0003, Harry Potter 3
4,B0001, Harry Potter 1
4,B0002, Harry Potter 2
4,B0003, Harry Potter 3

I wrote the following query and got the total books sold (sum of quantities) for product_id:

SELECT 
    transaction.txn_day, transaction.product_id,
    SUM(quantity) AS quantity 
FROM 
    transaction
GROUP BY
    transaction.product_id, transaction.txn_day;

I tried following query to get title_name for each product_id, but it seems incorrect.

SELECT 
    transaction.txn_day, transaction.product_id, catalog.title_name,
    SUM(quantity) AS quantity 
FROM 
    catalog
INNER JOIN 
    transaction ON catalog.product_id = transaction.product_id
GROUP BY
    transaction.txn_day, transaction.product_id, catalog.title_name;

I'm getting the following result:

|txn_day    |product_id |title_name     |quantity
|2019-03-01 |B0002      |Harry Potter 2 |9
|2019-03-01 |B0001      |Harry Potter 1 |12
|2019-03-01 |B0003      |Harry Potter 3 |3

I'm expecting the result to be something like:

|txn_day    | product_id | quantity | title_name
|2019-03-01 | B0003      | 1        | Harry Potter 3
|2019-03-01 | B0002      | 3        | Harry Potter 2
|2019-03-01 | B0001      | 4        | Harry Potter 1

Please suggest any changes required to the query.

Edit your question and provide: (1) The results you want. (2) A clear explaining of what "total books sold" really means. Your query seems to have nothing to do with that. (3) A database tag for the database you are using. — Gordon Linoff, Feb 21 '21 at 01:38
@Alexis.Rolland, it's summing up based on each occurance of book in catalog table. All I want from catalog is the name of the book. I've updated the post for the result I'm getting. — JKL, Feb 21 '21 at 02:18
Relationship is based on compound key of market_id and product_id. I prefer to avoid compound keys whenever possible. A unique record ID from catalog could be saved as foreign key in transaction. A table of unique product ID and name could also simplify this query linking. — June7, Feb 21 '21 at 06:31

score 2 · Accepted Answer · edited Feb 21 '21 at 04:32

I loaded your data and got the following results:

DATE        ID      TITLE           QTY
3/1/2019    B0002   Harry Potter 2  9
3/1/2019    B0003   Harry Potter 3  3
3/1/2019    B0001   Harry Potter 1  12

Your clue here as to what is wrong is that your Qty is 3x too much for each record.

If you look at your INNER JOIN you are only joining by the product_id.

Take a look at your transaction rows.

You can see that each product_id exists along with 3 different market_ids, that is why your qty is tripling.

Solution: Add the market_id to the JOIN:

SELECT   t.txn_day
        ,t.product_id
        ,c.title_name
        ,SUM(quantity) AS quantity
FROM     catalog c
         INNER JOIN transactions t ON c.product_id = t.product_id AND c.market_id = t.market_id
GROUP BY t.txn_day, t.product_id, c.title_name
order by c.title_name;

You will get your results:

3/1/2019    B0001   Harry Potter 1  4
3/1/2019    B0002   Harry Potter 2  3
3/1/2019    B0003   Harry Potter 3  1

score 1 · Answer 2 · answered Feb 21 '21 at 02:17

It's wrong because product_id and title_name are not unique in catalog. Consider:

SELECT transaction.txn_day, transaction.product_id, title_name,
sum(quantity) as qty FROM (SELECT DISTINCT product_id, title_name FROM catalog) AS cat
INNER JOIN transaction ON cat.product_id=transaction.product_id
group by transaction.txn_day, transaction.product_id, catalog.title_name;

Also, a field alias same as native field name might cause issue in some systems, it does in Access.

score 1 · Answer 3 · answered Feb 21 '21 at 02:19

I think you need to join on two columns, not one -- both the market and the product:

SELECT t.txn_day, product_id, c.title_name, SUM(quantity) as quantity
FROM catalog c INNER JOIN
     transaction t
     USING (product_id, market_id)
GROUP BY t.txn_day, product_id, c.title_name;

This uses the USING clause to simplify the JOIN condition. It also introduces table aliases so the query is easier to write and to read.

Erwin Brandstetter · Answer 4 · 2021-02-21T02:39:58.697

Your data suggests that product_id alone is the key to title_name. So:

SELECT t.*, c.title_name
FROM  (
   SELECT txn_day, product_id, sum(quantity) AS sum_quantity
   FROM   transaction
   GROUP  BY txn_day, product_id
   ) t
LEFT   JOIN (
   SELECT DISTINCT product_id, title_name
   FROM   catalog
   ) c USING (product_id);

You should really have a table product listing distinct products.
(Unless your sample data is misleading and only the combination (market_id, product_id) is unique - in which case it wouldn't make sense to omit market_id in the aggregation. Hence I am back to my first assumption.)

Barring that, I use the subquery SELECT DISTINCT product_id, title_name FROM catalog to derive that table on the fly.

LEFT JOIN instead of just JOIN is an insurance against eliminating rows if a product_id is not found in table catalog.

Also, it's typically cheaper to aggregate first and join later. See:

Query with LEFT JOIN not returning rows for count of 0

SQL query to find the total number of books sold for each day by merging 2 tables?

4 Answers4