SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query SELECT COUNT

    Hi.

    I have a table in a mysql db:

    HTML Code:
    NAME           ROLE           DATE                  TYPE
    
    MARK           STR            2008-04-28            B
    KARL           MAN            2008-04-28            A
    KARL           IRE            2008-04-28            A
    JIM            IRE            2008-04-28            A
    PATTY          MAN            2008-04-28            C
    PATTY          STR            2008-04-28            C  
    On this table execute this query:

    Code:
    SELECT
    COUNT(TYPE) AS tot
    FROM tbl_1 
    WHERE DATE BETWEEN '2008-04-28' AND '2008-04-28' 
    GROUP BY TYPE, DATE
    ORDER BY DATE
    The result is:

    NR 1 row for TYPE = B
    NR 3 row for TYPE = A
    NR 2 rows for TYPE = C

    As you can see the column NAME actually contains 4 names divided by ROLE.

    I want the query counts once the name:

    NR 1 row per TYPE = B ( NAME = MARK )
    NR 1 row per TYPE = A ( NAME = KARL )
    NR 1 row per TYPE = A ( NAME = JIM )
    NR 1 row per TYPE = C ( NAME = PATTY )

    Then 4 rows finals, instead of 6 extracts now with the query, which also counts double names.

    It's possible ?
    Thanks x your attention
    Viki
    Last edited by viki1967; Apr 29, 2008 at 07:39.

  2. #2
    Founder of Primal Skill Ltd. feketegy's Avatar
    Join Date
    Aug 2006
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's just a hunch: Don't use the GROUP BY part...

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by feketegy View Post
    It's just a hunch: Don't use the GROUP BY part...
    thanks x your reply, but:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

    PHP Code:
     
    SELECT
    COUNT
    (TYPE) AS tot
    FROM tbl_1 
    WHERE DATE BETWEEN 
    '2008-04-28' AND '2008-04-28' 
    ORDER BY DATE 

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i don't understand your question. can you post the desired output of the query?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    i don't understand your question. can you post the desired output of the query?
    Thanks x your reply; this is output of query, eliminate the duplicate :

    HTML Code:
    NAME           ROLE           DATE                  TYPE
    
    MARK           STR            2008-04-28            B
    KARL           MAN            2008-04-28            A
    JIM            IRE            2008-04-28            A
    PATTY          MAN            2008-04-28            C

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how did you decide which role to display for KARL and PATTY?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    how did you decide which role to display for KARL and PATTY?

    This is the problem...

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    no, how did you decide which role to display? you provided your desired output so you made the decision. i need to know the reasoning behind that decision.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    no, how did you decide which role to display? you provided your desired output so you made the decision. i need to know the reasoning behind that decision.
    The reasoning is eliminate the duplicate ... you need count the name only one even if there is this several times.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    so you don't care which role is displayed? do you even need to display the role?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  11. #11
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    so you don't care which role is displayed? do you even need to display the role?

    Yes, I see the role but count only one the name for that day...

    thanks...

  12. #12
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No idea? Any suggestions ?

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can exploit the fact that mysql has a non-standard GROUP BY syntax that allows you arbitrarily omit columns with unpredictable results. but since you want unpredictable, this should be OK for you:
    Code:
    select name
         , role
         , date
         , type
      from tbl_1
    group
        by name
         , date
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  14. #14
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    you can exploit the fact that mysql has a non-standard GROUP BY syntax that allows you arbitrarily omit columns with unpredictable results. but since you want unpredictable, this should be OK for you:
    Code:
    select name
         , role
         , date
         , type
      from tbl_1
    group
        by name
         , date
    thanks!


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
  •