1

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
w8lessly
  • 11
  • 1

3 Answers3

5

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
HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • So I should include all fields in the GROUP BY clause except the count? – w8lessly Jan 13 '16 at 13:26
  • Yes, when you are using aggregate functions all attributes selected which are not in an aggregate function should be in the group by – HoneyBadger Jan 13 '16 at 13:27
  • The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. – jarlh Jan 13 '16 at 13:35
  • If this answer provided you with a solution (which I suspect it does) you can accept it as answer – HoneyBadger Jan 13 '16 at 13:39
1

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.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
0
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
D Mayuri
  • 456
  • 2
  • 6
  • If there are no items in `buylog` table for that `owner_id` , `Buylog_count` will be a `NULL`, not `0`. It doesn't meet A/C. – Evaldas Buinauskas Jan 13 '16 at 13:39
  • 1
    That, and for the rest it's more or less a copy of @EvaldasBuinauskas's answer – HoneyBadger Jan 13 '16 at 13:40
  • And as far as copying goes, it's suspicious how much your answer resembles mine here: http://stackoverflow.com/questions/34762276/sql-use-results-of-one-query-as-search-criteria-for-another-query/34762926#comment57279741_34762926 – HoneyBadger Jan 13 '16 at 13:44
  • As I am new user of stackoveflow and not much friendly with the use of it. I am not getting recent actions before posting my answer. Need to refresh page before posting. Extremely sorry for the mistakes. Will take care of it in future. – D Mayuri Jan 13 '16 at 13:49