Problem whit count and group in SQL

**Miguel61** · Oct 10, 2009 06:05

Hi all.

I have this table in db MySQL:

Code:

ID	NAME_1		NAME_2		NAME_3
1 	Myriam		Vinc	 	Gris
2 	Myriam		Fer		Vinc
3 	Myriam		Cher		Vinc
4 	Robert		Vinc		Gris
5 	Maria		Ángie		Mónica
6 	José		Cher		Vinc
7 	Ricardo		Mónica		Vinc

I need this output:

Code:

NAME		COUNT
Vinc		6
Myriam		3
Monica		2
Gris		2
Cher		2
Robert		1
Maria		1
José		1
Ricardo		1
Fer		1
Angie		1

Can you help me ?
Thanks x your help.
Mike

**r937** · Oct 10, 2009 06:08

that's an odd looking table

Code:

SELECT name
     , COUNT(*) AS "count"
  FROM ( SELECT name_1
           FROM daTable
         UNION ALL
         SELECT name_2
           FROM daTable
         UNION ALL
         SELECT name_3
           FROM daTable
       ) AS d
GROUP
    BY name

**Miguel61** · Oct 10, 2009 06:16

thanks, but:

Unknown column 'name' in 'field list'

**r937** · Oct 10, 2009 06:55

aaargh, my bad, sorry

Code:

SELECT name
     , COUNT(*) AS "count"
  FROM ( SELECT name_1 AS name
           FROM daTable
         UNION ALL
         SELECT name_2
           FROM daTable
         UNION ALL
         SELECT name_3
           FROM daTable
       ) AS d
GROUP
    BY name

**Miguel61** · Oct 10, 2009 07:15

Great !!!!

Many thanks!

**Miguel61** · Oct 25, 2009 05:05

Hi all, I need your help

I have this two tables in db MySQL:

Code:

daTable_2006
ID	NAME_1		NAME_2		NAME_3
1 	Myriam		Vinc	 	Gris
2 	Myriam		Fer		Vinc
3 	Myriam		Cher		Vinc
4 	Robert		Vinc		Gris
5 	Maria		&#193;ngie		M&#243;nica
6 	Jos&#233;		Cher		Vinc
7 	Ricardo		M&#243;nica		Vinc

daTable_2009
ID	NAME_1		NAME_2		NAME_3
1 	Myriam		Vinc	 	Gris
2 	Myriam		Fer		Vinc
3 	Myriam		Cher		Vinc
4 	Robert		Myriam		Gris
5 	Maria		Fer		M&#243;nica
6 	Jos&#233;		Cher		Vinc
7 	Ricardo		M&#243;nica		Vinc

Whit this query ( credits r937 ) I have this output:

Code:

SELECT name
     , COUNT(*) AS strCount_2006
  FROM ( SELECT name_1 AS name
           FROM daTable_2006
         UNION ALL
         SELECT name_2
           FROM daTable_2006
         UNION ALL
         SELECT name_3
           FROM daTable_2006
       ) AS d
GROUP
    BY name


NAME		strCount_2006
Vinc		6
Myriam		3
Monica		2
Gris		2
Cher		2
Robert		1
Maria		1
Jos&#233;		1
Ricardo		1
Fer		1
Angie		1

But I need this other output:

Code:

NAME		strCount_2006	strCount_2009
Vinc		6		5
Myriam		3		4
Monica		2		2
Gris		2		2
Cher		2		2
Robert		1		1
Maria		1		1
Jos&#233;		1		1
Ricardo		1		1
Fer		1		2
Angie		1		0

Can you help me ?

**r937** · Oct 25, 2009 05:58

i guess i could help, but i don't really want to

why are there three similar columns (NAME_1, NAME_2, NAME_3) in your table?

this design breaks first normal form and requires complex and convoluted queries (as you have seen)

any chance you can change it?

and if not, why don't you simply run my earlier query twice, once for each year, and then lay out the results side-by-side with php?

User Tag List

Thread: Problem whit count and group in SQL

Thread Tools

Display

Problem whit count and group in SQL

Bookmarks

Bookmarks

Posting Permissions