join colums and count

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • gab
    New Member
    • Jul 2007
    • 5

    #1

    join colums and count

    Hi :-)

    I have a table with X and Y (which are strings), I would like to get an output in the form of one colum with X and Y together, and a colum with the number of times the strings is representet. I just can't get the X and Y colums to mix

    the below gives colums Z,Z,count(Z)

    [PHP]SELECT X as Z, Y as Z, COUNT(X) as count, COUNT(Y) AS count FROM `table` WHERE X=X OR Y=Y GROUP BY Z ORDER BY count[/PHP]

    any advice?
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    Can you please give some sample data and the required output?

    Comment

    • gab
      New Member
      • Jul 2007
      • 5

      #3
      sure :-)

      data

      X ||| Y
      name1 ||| name2
      name3 ||| name1
      name4 ||| name5
      name2 ||| name1


      Output:

      Z || count
      name1 || 3
      name2 || 2
      ... ||
      name5 || 1



      I'm thinking UNION, but haven't cracked it yet...

      Comment

      • mwasif
        Recognized Expert Contributor
        • Jul 2006
        • 802

        #4
        You are right, UNION is the way. Try this

        Code:
        SELECT Z, SUM(times) AS repeats FROM (
        (SELECT X AS Z, COUNT(X) AS times FROM data GROUP BY X)
        UNION ALL
        (SELECT Y AS Z, COUNT(Y) AS times FROM data GROUP BY Y)
        ) temp_tbl
        GROUP BY Z
        ORDER BY repeats DESC

        Comment

        • gab
          New Member
          • Jul 2007
          • 5

          #5
          Originally posted by mwasif
          You are right, UNION is the way. Try this

          Code:
          SELECT Z, SUM(times) AS repeats FROM (
          (SELECT X AS Z, COUNT(X) AS times FROM data GROUP BY X)
          UNION ALL
          (SELECT Y AS Z, COUNT(Y) AS times FROM data GROUP BY Y)
          ) temp_tbl
          GROUP BY Z
          ORDER BY repeats DESC
          You are correct it does the job :-D thanks alot, I really appreciate it.

          Comment

          Working...