SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT fields with specific number in 1 row?!?!

    How can I count how many fields in 1 row with, lets say, the number 3?

    My db table looks like this:
    id
    field1
    field2
    field3
    field4
    field5

    Now I want to count how many "3" there is in the fields of "field1" to "field5"...

    Thanks in advance!

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If your database design is as I think it is, you really need to reconsider your database design, so that there's an extra 'fields' table which references an ID and a name.

    Then you can do a simple joined select to get the number for each field.

    With your current design you'll need to run a SELECT COUNT for each field.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT COUNT(*)
      FROM foo
     WHERE 3 IN (field1, field2, field3...)
    edit-although if you want the query to use possible indexes you have on those fields, you'll need a little different query.

    I agree with arkinstall though, the db design smells bad, although we don't really know for sure given the info you provided. Consider reading up on database normalization.

    Btw- sitepoint has forums specifically for database questions.

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    Code:
    SELECT COUNT(*)
      FROM foo
     WHERE 3 IN (field1, field2, field3...)
    I agree with arkinstall though, the db design smells bad, although we don't really know for sure given the info you provided. Consider reading up on database normalization.

    Btw- sitepoint has forums specifically for database questions.
    I'm afraid the db has to be like it is, and the above example only count how many rows with "3" the db has... What I need is to figure out how many "3" one single row has... Something like "WHERE id=?".

    So if A row has data like this:
    1(id),
    3,
    4,
    3,
    5,
    5,

    Then the outcome should be "2"... Hope this makes sense ;-)

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still... Any ideas?!?!?

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT (CASE field1 WHEN 3 THEN 1 ELSE 0 END)
         + (CASE field2 WHEN 3 THEN 1 ELSE 0 END)
         + (CASE field3 WHEN 3 THEN 1 ELSE 0 END) cnt
      FROM foo
     WHERE id = 9999
    I would just do it in php though.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Not positive but I believe your looking at a group with group level filtering.

    Code SQL:
    SELECT
    	 id
      FROM
         TABLE
     GROUP
        BY
         id
    HAVING
         COUNT(*) = 3

  8. #8
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am not able to understand your doubt.. be still more specific

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oddz View Post
    Not positive but I believe your looking at a group with group level filtering.
    nope

    the problem was to perform the count on the columns of a single row

    see post #6 for the solution
    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
  •