How can I mix COUNT() and non-COUNT() columns without losing information in the query?

Question

I started with a query:

SELECT strip.name as strip, character.name as character
  from strips, characters, appearances
 where strips.id = appearances.strip_id
   and characters.id = appearances.character.id
   and appearances.date in (...)

Which yielded me some results:

strip                 | character
'Calvin & Hobbes'     | 'Calvin'
'Calvin & Hobbes'     | 'Hobbes'
'Pearls Before Swine' | 'Pig'
'Pearls Before Swine' | 'Rat'
'Pearls Before Swine' | 'Hobbes'  # a guest appearance
'Pearls Before Swine' | 'Calvin'  # a guest appearance

Then I wanted to also get the COUNT of the number of times a character is used (in any strip) within the result set. So I tried:

SELECT count(character.id), strip.name as strip, character.name as character
  from strips, characters, appearances
 where strips.id = appearances.strip_id
   and characters.id = appearances.character.id
   and appearances.date in (...)

But that gave me

[ERROR 11:20:17] Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

So I tried:

SELECT count(character.id), strip.name as strip, character.name as character
  from strips, characters, appearances
 where strips.id = appearances.strip_id
   and characters.id = appearances.character.id
   and appearances.date in (...)
 group by character.id

Which gave me

count | strip                 | character
4     | 'Calvin & Hobbes'     | 'Calvin'
4     | 'Calvin & Hobbes'     | 'Hobbes'
2     | 'Pearls Before Swine' | 'Pig'
2     | 'Pearls Before Swine' | 'Rat'

That is, I lose all the extra information about exactly which characters appear in which strips.

What I'd like to get is this:

count | strip                 | character
4     | 'Calvin & Hobbes'     | 'Calvin'
4     | 'Calvin & Hobbes'     | 'Hobbes'
2     | 'Pearls Before Swine' | 'Pig'
2     | 'Pearls Before Swine' | 'Rat'
4     | 'Pearls Before Swine' | 'Calvin'
4     | 'Pearls Before Swine' | 'Hobbes'

But I can't seem to figure it out. I'm on MySQL if it matters. Perhaps it'll just take two queries.

Answer 1

On an RDBMS system, I'd select the count query into a temporary table, then rejoin the temp table to the main table. This will give you the best of both worlds.

I don't know if mySQL supports temp tables, though.

Answer 2

If MySQL supports Analytics/Window Functions, then:

select bar, yoo,
    count(yoo) over (partition by yoo) c 
from t
/

otherwise you'll need to use a correlated subquery:

select bar, yoo,
    (select count(yoo) from t t2 where t2.yoo=t.yoo) c 
from t
/

on oracle, the test data looks like:

create table t(bar number, yoo varchar2(16));

insert into t(bar, yoo) values (1, 'hello');

insert into t(bar, yoo) values (2, 'goodbye');

insert into t(bar, yoo) values (3, 'goodbye');

insert into t(bar, yoo) values (4, 'goodbye');

insert into t(bar, yoo) values (2, 'calvin');

insert into t(bar, yoo) values (5, 'Hobbes');

insert into t(bar, yoo) values (6, 'Hobbes');

commit;

Answer 3

Subselect:

SELECT foo.bar
    ,baz.yoo
    ,(SELECT COUNT(*) FROM baz AS baz2 WHERE baz2.yoo = baz.yoo etc.) AS yoo_count
FROM foo, baz
WHERE foo.baz_id = baz.id
    AND baz.id in (...)

You can also take the subselect and nest it and join.

Answer 4

What about grouping by foo.bar?

SELECT count(baz.id) as count, foo.bar, baz.yoo where foo.baz_id = baz.id and baz.id in (...) group by foo.bar

Answer 5

Does mySQL support analytic functions? Like:

SELECT foo.bar, baz.yoo, count(baz.yoo) over (partition by foo.bar) as yoo_count 
from foo, bar
where foo.baz_id = baz.id and baz.id in (...)

Alternatively:

SELECT foo.bar, baz.yoo, v.yoo_count 
from foo, bar, 
( select foo.baz_id, count(*) as yoo_count
  from foo
  group by foo.baz_id
) as v
where foo.baz_id = baz.id and baz.id in (...)
and v.baz_id = foo.baz_id;

Answer 6

If I'm going to be requiring a count field like this reasonably often then I create a user defined function to return the value and just use that inside a normal select.