CODEWITHSUNDEEP
sqlsql-server
w8lessly
w8lessly

Reputation: 11

I can't get my desired SQL result

I have two tables:

Owners

+----+------+------------+
| id | name | birth_year |
+----+------+------------+
|  1 | John |       1970 |
|  2 | Jane |       1980 |
|  3 | Jack |       1990 |
|  4 | Josh |       2000 |
+----+------+------------+

Buylog

+----+----------+------------+
| id | owner_id |    date    |
+----+----------+------------+
|  1 |        1 | 01/01/2016 |
|  2 |        2 | 01/02/2016 |
|  3 |        2 | 01/03/2016 |
|  4 |        1 | 01/04/2016 |
+----+----------+------------+

I need to get all the info from Owners table plus the count of buys per owner:

+-----------+-------------+-------------------+--------------+
| owners.id | owners.name | owners.birth_year | buylog.Count |
+-----------+-------------+-------------------+--------------+
|         1 | John        |              1970 |            2 |
|         2 | Jane        |              1980 |            2 |
|         3 | Jack        |              1990 |            0 |
|         4 | Josh        |              2000 |            0 |
+-----------+-------------+-------------------+--------------+

I tried the below query, but that returns with error:

Select
  o.id,
  o.name,
  o.birth_year,
  Count(b.id) as Count
From
  owners o
Left Outer Join
  buylog b
On
  b.owner_id = o.id

Upvotes: 1

Views: 57

Answers (3)

D Mayuri
D Mayuri

Reputation: 456

SELECT o.*, 
CASE 
    WHEN temp.Buylog_count IS NULL THEN 0
    ELSE temp.Buylog_count
END
FROM Owners o 
LEFT JOIN
(
    SELECT b.owner_id AS oid, COUNT(*) AS Buylog_count
    FROM Buylog b   
    GROUP BY b.owner_id 
)temp ON temp.oid = o.id

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Query by HoneyBadger will do just fine, however this might perform better:

SELECT o.id
    , o.name
    , o.birth_year
    , COALESCE(b.Count, 0) AS Count
FROM owners o
LEFT JOIN (
    SELECT owner_id, COUNT(*) AS Count
    FROM buylog
    GROUP BY owner_id
    ) AS b
    ON b.owner_id = o.id;

It should bring exactly the same result.

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

The error message should be pretty clear, you are missing a group by clause: 

  Select
  o.id,
  o.name,
  o.birth_year,
  Count(b.id) as Count
From
  owners o
Left Outer Join
  buylog b
On
  b.owner_id = o.id
Group By o.id,
  o.name,
  o.birth_year

Upvotes: 5

Related Questions