Query to get a breakdown of profile fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Steve Machol
    Former Customer Support Manager
    • Jul 2000
    • 154488

    Query to get a breakdown of profile fields

    I need one query that will return the count results of the entries in a specific field. For instance here are sample queries to return the counts for each profile entry:

    SELECT COUNT(*) FROM `userfield` where field8='A';
    SELECT COUNT(*) FROM `userfield` where field8='B';
    SELECT COUNT(*) FROM `userfield` where field8='C';
    etc.

    What would one query be to show the counts for each field entry? I have 12 options in total.
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


  • Colin F
    Senior Member
    • May 2004
    • 17689

    #2
    SELECT field8, COUNT( field8 )
    FROM userfield
    #WHERE field8 IN ('A','B','C')
    GROUP BY field8;


    You can leave out the WHERE line if you want it to return a count for each entry.
    Best Regards
    Colin Frei

    Please don't contact me per PM.

    Comment

    • Steve Machol
      Former Customer Support Manager
      • Jul 2000
      • 154488

      #3
      Thanks, but it didn't work:

      SELECT field8, COUNT( field8 )
      FROM userfield
      #WHERE field8 IN ('Dispensing Optician','Optometrist','Ophthalmologist','Ophthalmic Technician','Optical Retail','Optical Laboratory Technician','Optical Wholesale Lab (other positions)','Lens Manufacturer','Frame Manufacturer','Other Optical Manufacturer or Vendor','Consumer or Non-Eyecare field')
      GROUP BY field8;

      #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
      Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
      Change CKEditor Colors to Match Style (for 4.1.4 and above)

      Steve Machol Photography


      Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


      Comment

      • Colin F
        Senior Member
        • May 2004
        • 17689

        #4
        The WHERE clause is commented by the way. Remove the # sign to uncomment it.

        The query works fine for me on MySQL 4.1.21

        Try just this:

        SELECT field8, COUNT( field8 )
        FROM userfield
        GROUP BY field8;
        Best Regards
        Colin Frei

        Please don't contact me per PM.

        Comment

        • Steve Machol
          Former Customer Support Manager
          • Jul 2000
          • 154488

          #5
          That works. Thanks!

          Anyway to get the results orders by count instead of field name?
          Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
          Change CKEditor Colors to Match Style (for 4.1.4 and above)

          Steve Machol Photography


          Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


          Comment

          • Colin F
            Senior Member
            • May 2004
            • 17689

            #6
            Untested:

            SELECT field8, COUNT( field8 ) AS count
            FROM userfield
            GROUP BY field8
            ORDER BY count;
            Best Regards
            Colin Frei

            Please don't contact me per PM.

            Comment

            • Steve Machol
              Former Customer Support Manager
              • Jul 2000
              • 154488

              #7
              Excellent - thanks!

              I made one small change to show in descending order:

              SELECT field8, COUNT( field8 ) AS count
              FROM userfield
              GROUP BY field8
              ORDER BY count DESC
              LIMIT 0 , 30
              Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
              Change CKEditor Colors to Match Style (for 4.1.4 and above)

              Steve Machol Photography


              Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...