Counting problem

Hi all

I need your help here, I've been trying to solve this for hours and it's
driving me crazy. It's yet another counting problem.

Let's say I have a "friends" table looking like this:

+-------------+----------------+--------+
| Town | Name | Eyes |
+-------------+----------------+--------+
| Paris | Nicolas | blue |
| Paris | Claire | blue |
| Paris | Simon | brown |
| Paris | Marie | black |
| NY | Jason | blue |
| NY | Frank | green |
| NY | Amy | blue |
+-------------+----------------+--------+

What I want is to know the number of friends with a particular eye color
living in each town, like this:

+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+

I've always been very bad in mastering JOINs, and here I'm at a complete
loss.

Is there a charitable soul out there that can show me how to solve this?

Thank you very much.
Tom

--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

Thomas Guignard wrote:[color=blue]
> Hi all
>
> I need your help here, I've been trying to solve this for hours and it's
> driving me crazy. It's yet another counting problem.
>
> Let's say I have a "friends" table looking like this:
>
> +-------------+----------------+--------+
> | Town | Name | Eyes |
> +-------------+----------------+--------+
> | Paris | Nicolas | blue |
> | Paris | Claire | blue |
> | Paris | Simon | brown |
> | Paris | Marie | black |
> | NY | Jason | blue |
> | NY | Frank | green |
> | NY | Amy | blue |
> +-------------+----------------+--------+
>
> What I want is to know the number of friends with a particular eye color
> living in each town, like this:
>
> +------------+------+-------+-------+-------+
> | Town | blue | brown | green | black |
> +------------+------+-------+-------+-------+
> | NY | 2 | 0 | 1 | 0 |
> | Paris | 2 | 1 | 0 | 1 |
> +------------+------+-------+-------+-------+
>
> I've always been very bad in mastering JOINs, and here I'm at a complete
> loss.
>
> Is there a charitable soul out there that can show me how to solve this?
>
> Thank you very much.
> Tom
>[/color]
quick and dirty...
SELECT Town, COUNT(IF(Eyes='blue',1,NULL)) AS blue, .... FROM friends
GROUP BY Town;

There must be a more elegant way

Bruce

re: Counting problem

Thomas Guignard wrote:[color=blue]
> Hi all
>
> I need your help here, I've been trying to solve this for hours and it's
> driving me crazy. It's yet another counting problem.
>
> Let's say I have a "friends" table looking like this:
>
> +-------------+----------------+--------+
> | Town | Name | Eyes |
> +-------------+----------------+--------+
> | Paris | Nicolas | blue |
> | Paris | Claire | blue |
> | Paris | Simon | brown |
> | Paris | Marie | black |
> | NY | Jason | blue |
> | NY | Frank | green |
> | NY | Amy | blue |
> +-------------+----------------+--------+
>
> What I want is to know the number of friends with a particular eye color
> living in each town, like this:
>
> +------------+------+-------+-------+-------+
> | Town | blue | brown | green | black |
> +------------+------+-------+-------+-------+
> | NY | 2 | 0 | 1 | 0 |
> | Paris | 2 | 1 | 0 | 1 |
> +------------+------+-------+-------+-------+
>
> I've always been very bad in mastering JOINs, and here I'm at a complete
> loss.
>
> Is there a charitable soul out there that can show me how to solve this?
>
> Thank you very much.
> Tom
>[/color]
quick and dirty...
SELECT Town, COUNT(IF(Eyes='blue',1,NULL)) AS blue, .... FROM friends
GROUP BY Town;

There must be a more elegant way

Bruce

re: Counting problem

Bruce Wolk <fake@not-a-real-address.net> wrote in message news:<QBKyb.28618$nI7.25581@newssvr29.news.prodigy .com>...[color=blue]
> Thomas Guignard wrote:[color=green]
> > Hi all
> >
> > I need your help here, I've been trying to solve this for hours and it's
> > driving me crazy. It's yet another counting problem.
> >
> > Let's say I have a "friends" table looking like this:
> >
> > +-------------+----------------+--------+
> > | Town | Name | Eyes |[/color]
> +-------------+----------------+--------+[color=green]
> > | Paris | Nicolas | blue |
> > | Paris | Claire | blue |
> > | Paris | Simon | brown |
> > | Paris | Marie | black |
> > | NY | Jason | blue |
> > | NY | Frank | green |
> > | NY | Amy | blue |
> > +-------------+----------------+--------+
> >
> > What I want is to know the number of friends with a particular eye color
> > living in each town, like this:
> >
> > +------------+------+-------+-------+-------+
> > | Town | blue | brown | green | black |[/color]
> +------------+------+-------+-------+-------+[color=green]
> > | NY | 2 | 0 | 1 | 0 |
> > | Paris | 2 | 1 | 0 | 1 |
> > +------------+------+-------+-------+-------+
> >
> > I've always been very bad in mastering JOINs, and here I'm at a complete
> > loss.
> >
> > Is there a charitable soul out there that can show me how to solve this?[/color][/color]

SELECT DISTINCT TOWN,
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='black',1,0)) as 'Black',
SUM(IF(EYES='gray',1,0)) as 'Gray',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(CASE
WHEN EYES = 'blue' THEN 0
WHEN EYES = 'brown' THEN 0
WHEN EYES = 'black' THEN 0
WHEN EYES = 'gray' THEN 0
WHEN EYES = 'green' THEN 0
ELSE 1
END ) as 'Other'
FROM friends
GROUP BY town;

re: Counting problem

Bruce Wolk <fake@not-a-real-address.net> wrote in message news:<QBKyb.28618$nI7.25581@newssvr29.news.prodigy .com>...[color=blue]
> Thomas Guignard wrote:[color=green]
> > Hi all
> >
> > I need your help here, I've been trying to solve this for hours and it's
> > driving me crazy. It's yet another counting problem.
> >
> > Let's say I have a "friends" table looking like this:
> >
> > +-------------+----------------+--------+
> > | Town | Name | Eyes |[/color]
> +-------------+----------------+--------+[color=green]
> > | Paris | Nicolas | blue |
> > | Paris | Claire | blue |
> > | Paris | Simon | brown |
> > | Paris | Marie | black |
> > | NY | Jason | blue |
> > | NY | Frank | green |
> > | NY | Amy | blue |
> > +-------------+----------------+--------+
> >
> > What I want is to know the number of friends with a particular eye color
> > living in each town, like this:
> >
> > +------------+------+-------+-------+-------+
> > | Town | blue | brown | green | black |[/color]
> +------------+------+-------+-------+-------+[color=green]
> > | NY | 2 | 0 | 1 | 0 |
> > | Paris | 2 | 1 | 0 | 1 |
> > +------------+------+-------+-------+-------+
> >
> > I've always been very bad in mastering JOINs, and here I'm at a complete
> > loss.
> >
> > Is there a charitable soul out there that can show me how to solve this?[/color][/color]

SELECT DISTINCT TOWN,
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='black',1,0)) as 'Black',
SUM(IF(EYES='gray',1,0)) as 'Gray',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(CASE
WHEN EYES = 'blue' THEN 0
WHEN EYES = 'brown' THEN 0
WHEN EYES = 'black' THEN 0
WHEN EYES = 'gray' THEN 0
WHEN EYES = 'green' THEN 0
ELSE 1
END ) as 'Other'
FROM friends
GROUP BY town;

re: Counting problem

> SELECT DISTINCT TOWN,[color=blue]
> SUM(IF(EYES='blue',1,0)) as 'Blue',
> SUM(IF(EYES='brown',1,0)) as 'Brown'
> FROM friends
> GROUP BY town;[/color]

Yesss, thank you, it did the trick. Now I'm looking for a convenient way
to add a row with the totals at the end of the table, like this:

+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
| Total: | 4 | 1 | 1 | 1 |
+------------+------+-------+-------+-------+

Thank you for your help.
Tom


--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

> SELECT DISTINCT TOWN,[color=blue]
> SUM(IF(EYES='blue',1,0)) as 'Blue',
> SUM(IF(EYES='brown',1,0)) as 'Brown'
> FROM friends
> GROUP BY town;[/color]

Yesss, thank you, it did the trick. Now I'm looking for a convenient way
to add a row with the totals at the end of the table, like this:

+------------+------+-------+-------+-------+
| Town | blue | brown | green | black |
+------------+------+-------+-------+-------+
| NY | 2 | 0 | 1 | 0 |
| Paris | 2 | 1 | 0 | 1 |
+------------+------+-------+-------+-------+
| Total: | 4 | 1 | 1 | 1 |
+------------+------+-------+-------+-------+

Thank you for your help.
Tom


--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

> Yesss, thank you, it did the trick. Now I'm looking for a convenient way[color=blue]
> to add a row with the totals at the end of the table, like this:[/color]

without ROLLUP that is, because ROLLUP is not yet supported by the
version of MySQL I am using (and no, I can't update it, since it's not
my server).


--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

> Yesss, thank you, it did the trick. Now I'm looking for a convenient way[color=blue]
> to add a row with the totals at the end of the table, like this:[/color]

without ROLLUP that is, because ROLLUP is not yet supported by the
version of MySQL I am using (and no, I can't update it, since it's not
my server).


--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

OK, never mind, I found it using UNION:

SELECT DISTINCT TOWN,
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(IF(EYES='black',1,0)) as 'Black'
FROM friends
GROUP BY town
UNION SELECT 'Total' AS 'Town',
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(IF(EYES='black',1,0)) as 'Black'
FROM friends
[color=blue]
> +------------+------+-------+-------+-------+
> | Town | blue | brown | green | black |
> +------------+------+-------+-------+-------+
> | NY | 2 | 0 | 1 | 0 |
> | Paris | 2 | 1 | 0 | 1 |
> +------------+------+-------+-------+-------+
> | Total: | 4 | 1 | 1 | 1 |
> +------------+------+-------+-------+-------+[/color]

--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne

re: Counting problem

OK, never mind, I found it using UNION:

SELECT DISTINCT TOWN,
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(IF(EYES='black',1,0)) as 'Black'
FROM friends
GROUP BY town
UNION SELECT 'Total' AS 'Town',
SUM(IF(EYES='blue',1,0)) as 'Blue',
SUM(IF(EYES='brown',1,0)) as 'Brown',
SUM(IF(EYES='green',1,0)) as 'Green',
SUM(IF(EYES='black',1,0)) as 'Black'
FROM friends
[color=blue]
> +------------+------+-------+-------+-------+
> | Town | blue | brown | green | black |
> +------------+------+-------+-------+-------+
> | NY | 2 | 0 | 1 | 0 |
> | Paris | 2 | 1 | 0 | 1 |
> +------------+------+-------+-------+-------+
> | Total: | 4 | 1 | 1 | 1 |
> +------------+------+-------+-------+-------+[/color]

--
Thomas Guignard
Acoustics and Electromagnetism Lab
Swiss Federal Institute of Technology, Lausanne