Selecting column from one table and count from another

Question

t1

id | name | include
-------------------
1  | foo  |  true
2  | bar  |  true
3  | bum  |  false

t2

id | some | table_1_id
-------------------------
1  | 42   |     1
2  | 43   |     1
3  | 42   |     2
4  | 44   |     1
5  | 44   |     3

Desired output:

name | count(some)
------------------
foo  | 3
bar  | 1

What I have currently from looking through other solutions here:

    SELECT      a.name, 
            COUNT(r.some)

FROM        t1 a
JOIN  t2 r on a.id=r.table_1_id
WHERE       a.include = 'true' 

GROUP BY    a.id,
            r.some;

but that seems to get me

name | count(r.some)
--------------------
foo  | 1
foo  | 1
bar  | 1
foo  | 1

I'm no sql expert (I can do simple queries) so I'm googling around as well but finding most of the solutions I find give me this result. I'm probably missing something really easy.

juergen d · Accepted Answer · 2013-07-24T10:41:31.987

11

Just remove the second column from the group by clause

SELECT      a.name, 
            COUNT(r.some)    
FROM        t1 a
JOIN  t2 r on a.id=r.table_1_id
WHERE       a.include = 'true' 
GROUP BY    a.name

Columns you want to use in an aggregate function like sum() or count() must be left out of the group by clause. Only put the columns in there you want to be unique outputted.

edited Jul 24 '13 at 10:41

answered Jul 18 '13 at 16:59

juergen d

201,996
37
293
362

You are pulling the `a.name` column even though you are not grouping on it – would that compile in Oracle? – Andriy M Jul 23 '13 at 18:16

score 1 · Answer 2 · edited May 23 '17 at 12:32

This is because multiple column group requires the all column values to be same.

See this link for more info., Using group by on multiple columns

Actually in you case., if some are equal, table_1_id is not equal (And Vice versa). so grouping cannot occur. So all are displayed individually.

If the entries are like,

id | some | table_1_id
-------------------------
1  | 42   |     1
2  | 43   |     1
3  | 42   |     2
4  | 42   |     1

Then the output would have been.,

name | count
------------------
foo  | 2     (for 42)
foo  | 1     (for 43)
bar  | 1     (for 42)

Actually, if you want to group on 1 column as Juergen said, you could remove r.some; from groupby clause.

Thanks for the reading link, it makes more sense now. Wish I could give 2 check-marks :( — Th3sandm4n, Jul 18 '13 at 19:46

Selecting column from one table and count from another

2 Answers2