Multiple counts using grouping, ordering, and joins

[Jim]

I'm having trouble doing multiple counts with groups, ordering, and
joins. Can someone help me out? Below is two table samples and what
I'm trying to get my output to be:

TABLEA
ID CHIP
----------- ----------
1 Lays
1 Ruffles
1 Ruffles
1 Ruffles
1 Doritos
1 Doritos
2 Lays
2 Lays
2 Doritos
3 Ruffles
TABLEB
ID NAME
----------- ----------
1 Jim
2 Bob
3 MISSY
What I'm hoping to get is the following rows:

Bob 2 0 1
Jim 1 3 2
MISSY 0 0 1
This is the name, count(lays), count(ruffles), count(doritos) ordered
by name. I thought it would be rather easy but can't seem to get it
for some reason. Any help would be greatly appreciated!

[Knut Stolze]

Jim wrote:

I'm having trouble doing multiple counts with groups, ordering, and
joins. Can someone help me out? Below is two table samples and what
I'm trying to get my output to be:

TABLEA
ID CHIP
----------- ----------
1 Lays
1 Ruffles
1 Ruffles
1 Ruffles
1 Doritos
1 Doritos
2 Lays
2 Lays
2 Doritos
3 Ruffles
TABLEB
ID NAME
----------- ----------
1 Jim
2 Bob
3 MISSY
What I'm hoping to get is the following rows:

Bob 2 0 1
Jim 1 3 2
MISSY 0 0 1
This is the name, count(lays), count(ruffles), count(doritos) ordered
by name. I thought it would be rather easy but can't seem to get it
for some reason. Any help would be greatly appreciated!

SELECT b.name,
( SELECT COUNT(*) FROM tablea AS a
WHERE a.id = b.id AND a.chip = 'Lays' ),
( SELECT COUNT(*) FROM tablea AS a
WHERE a.id = b.id AND a.chip = 'Ruffles' ),
( SELECT COUNT(*) FROM tablea AS a
WHERE a.id = b.id AND a.chip = 'Doritos' )
FROM tableb AS b
ORDER BY b.name
or:

SELECT b.name,
SUM(CASE WHEN a.chip = 'Lays' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Ruffles' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Doritos' THEN 1 ELSE 0 END)
FROM tableb AS b JOIN tablea AS a ON
( a.id = b.id )
ORDER BY b.name
Most probably, you have a whole bunch of other ways to express your query.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Tramm66]

"Jim" <ji*****@hotmai l.com> wrote in message
news:39******** *************** ***@posting.goo gle.com...

What I'm hoping to get is the following rows:

Bob 2 0 1
Jim 1 3 2
MISSY 0 0 1
This is the name, count(lays), count(ruffles), count(doritos) ordered
by name. I thought it would be rather easy but can't seem to get it
for some reason. Any help would be greatly appreciated!

One way would be following:

SELECT b.name,
SUM(CASE WHEN a.chip = 'Lays' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Ruffles' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Doritos' THEN 1 ELSE 0 END)
FROM TableA a, TableB b
WHERE a.id = b.id
GROUP BY b.name

HTH,
Tramm66

[Jim]

"Tramm66" <tr*****@hotmai l.com> wrote in message news:<40******* ***@news.estpak .ee>...

"Jim" <ji*****@hotmai l.com> wrote in message
news:39******** *************** ***@posting.goo gle.com...

What I'm hoping to get is the following rows:

Bob 2 0 1
Jim 1 3 2
MISSY 0 0 1
This is the name, count(lays), count(ruffles), count(doritos) ordered
by name. I thought it would be rather easy but can't seem to get it
for some reason. Any help would be greatly appreciated!

One way would be following:

SELECT b.name,
SUM(CASE WHEN a.chip = 'Lays' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Ruffles' THEN 1 ELSE 0 END),
SUM(CASE WHEN a.chip = 'Doritos' THEN 1 ELSE 0 END)
FROM TableA a, TableB b
WHERE a.id = b.id
GROUP BY b.name

HTH,
Tramm66

Thanks... I ended up using

select rtrim(name), count(case when chip='Lays' then 1 end) as Lays,
count(case when chip='Ruffles' then 1 end) as Ruffles, count(case when
chip='Doritos' then 1 end) as Doritos from tableb, tablea where
tableb.id = tablea.id group by name order by 1

Are any of these more efficient than the other? Thanks!

[Philip Sherman]

select rtrim(name), count(case when chip='Lays' then 1 end) as Lays,
count(case when chip='Ruffles' then 1 end) as Ruffles, count(case when
chip='Doritos' then 1 end) as Doritos from tableb, tablea where
tableb.id = tablea.id group by name order by 1

Are any of these more efficient than the other? Thanks!

Try an "explain" on each of the potential candidates and see which one
performs the least work. Explain is documented in SQL Reference V2.

Phil Sherman