CODEWITHSUNDEEP
mysqlsql
plexcell
plexcell

Reputation: 1677

SQL Query With Joins Selects Only 1 row

I'm having a problem with my SQL; the query selects only one row, even there are more rows it should select.

Select statement

SELECT p.id,
       p.product_name,
       p.product_date,
       coalesce(t.unique_hits, 0) AS unique_hits,
       coalesce(t.total_hits, 0) AS total_hits,
       coalesce(COUNT(s.product_id), 0) AS total_sales,
       coalesce(SUM(s.amount), 0) AS total_revenue
FROM products p
LEFT OUTER JOIN tracking_hits t ON p.id = t.product_id
LEFT OUTER JOIN transactions s ON p.id = s.product_id
WHERE p.vendor_id = 10;

Example with DDL here:
http://sqlfiddle.com/#!2/b393df/3

I added two products with same vendor id and when I run the query, it selects only one product, even it should select both.

SQL to create tables and data

CREATE TABLE products(id int, product_name varchar(100)
      , product_date varchar(100), vendor_id int);

CREATE TABLE tracking_hits(id int, product_id int, unique_hits int
      , total_hits int);

CREATE TABLE transactions(id int, product_id int, amount double);

Insert some data

INSERT INTO products(id, product_name, product_date, vendor_id)
VALUES(0, "Product", "2014-05-02", 10);

INSERT INTO products(id, product_name, product_date, vendor_id)
VALUES(1, "Product", "2014-05-02", 10);

INSERT INTO tracking_hits(id, product_id, unique_hits, total_hits)
VALUES(0, 0, 10, 52);

INSERT INTO tracking_hits(id, product_id, unique_hits, total_hits)
VALUES(1, 1, 52, 124);    

INSERT INTO transactions(id, product_id, amount)
VALUES(0, 0, 19.00);
    
INSERT INTO transactions(id, product_id, amount)
VALUES(1, 0, 19.00);

INSERT INTO transactions(id, product_id, amount)
VALUES(2, 0, 19.00);

INSERT INTO transactions(id, product_id, amount)
VALUES(3, 1, 29.00);

Whats the problem here?

Upvotes: 1

Views: 39

Answers (3)

SebastianH
SebastianH

Reputation: 2182

The basic problem is the missing GROUP BY while using aggregations like SUM and COUNT. Besides that I think you could simplify your query quite a bit by avoiding all those coalesce functions. The DBMS should be smart enough to handle null values as 0 without explicitely telling it to do so. That would change if you wanted to have a different value than 0 there. Here is a working (sqlfiddle) example:

SELECT p.id,
       p.product_name,
       p.product_date,
       t.unique_hits AS unique_hits,
       t.total_hits AS total_hits,
       COUNT(s.product_id) AS total_sales,
       SUM(s.amount) AS total_revenue
FROM products p
LEFT JOIN tracking_hits t ON p.id = t.product_id
LEFT JOIN transactions s ON p.id = s.product_id
WHERE p.vendor_id = 10
GROUP BY p.id, p.product_name, p.product_date, t.unique_hits, t.total_hits;

UPDATE: I added t.unique_hits, t.total_hits to the GROUP BY too.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Here is your query:

SELECT p.id,
       p.product_name,
       p.product_date,
       coalesce(t.unique_hits, 0) AS unique_hits,
       coalesce(t.total_hits, 0) AS total_hits,
       coalesce(COUNT(s.product_id), 0) AS total_sales,
       coalesce(SUM(s.amount), 0) AS total_revenue
FROM products p
LEFT OUTER JOIN tracking_hits t ON p.id = t.product_id
LEFT OUTER JOIN transactions s ON p.id = s.product_id
WHERE p.vendor_id = 10;

It is an aggregation query without a group by (because of the use of the aggregation functions sum() and count() in the select). Such a query always returns one row. I think you want:

SELECT p.id,
       p.product_name,
       p.product_date,
       coalesce(t.unique_hits, 0) AS unique_hits,
       coalesce(t.total_hits, 0) AS total_hits,
       coalesce(COUNT(s.product_id), 0) AS total_sales,
       coalesce(SUM(s.amount), 0) AS total_revenue
FROM products p
LEFT OUTER JOIN tracking_hits t ON p.id = t.product_id
LEFT OUTER JOIN transactions s ON p.id = s.product_id
WHERE p.vendor_id = 10
GROUP BY p.id, t.unique_hits, t.total_hits;

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You are missing group by while doing the aggregate function sum()

SELECT p.id,
       p.product_name,
       p.product_date,
       coalesce(t.unique_hits, 0) AS unique_hits,
       coalesce(t.total_hits, 0) AS total_hits,
       coalesce(COUNT(s.product_id), 0) AS total_sales,
       coalesce(SUM(s.amount), 0) AS total_revenue
FROM products p
LEFT OUTER JOIN tracking_hits t ON p.id = t.product_id
LEFT OUTER JOIN transactions s ON p.id = s.product_id
WHERE p.vendor_id = 10
group by p.id
;

Upvotes: 1

Related Questions