Announcement

Collapse
No announcement yet.

Query to get a breakdown of profile fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.



  • #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


    • #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


      • #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


        • #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


          • #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


            • #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...
              X