SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem whit count and group in SQL

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks, but:

    Unknown column 'name' in 'field list'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Great !!!!

    Many thanks!

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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		Ángie		Mónica
    6 	José		Cher		Vinc
    7 	Ricardo		Mó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ónica
    6 	José		Cher		Vinc
    7 	Ricardo		Mó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é		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é		1		1
    Ricardo		1		1
    Fer		1		2
    Angie		1		0
    Can you help me ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •