Post reply

need help with COUNT

  • July 31, 2003 at 2:49 am

    #159827

    i am tried to COUNT correct but no success

    what i need is to COUNT

    the field [id]

    -----------------------------------

    SELECT COUNT(id) AS Count_id, mhlka, sog,fname

    FROM dbo.searchall

    GROUP BY Fname, mhlka,sog, fld2, mhlka_id, fld1

    HAVING (fld2 = 2) AND (mhlka_id = 2)

    and Fname LIKE '%%%'

    AND mhlka_id in (2)

    AND fld2 in (2)

    AND (fld1 BETWEEN '01/01/2002' AND '01/01/2009')

    -------------------------------

    like this i get the field [FNAME] many tims

    thnks

    ilan

  • July 31, 2003 at 3:25 am

    #467905

    Maybe you query overuses HAVING and WHERE would be most appropriate. Can you state what the query is trying to accomplish?

    Cheers,

    - Mark


    Cheers,
    - Mark

  • July 31, 2003 at 3:32 am

    #467910

    this

    ---------------------------------------------FNAME MHLKA SOG COUNT_ID

    -------------------------------------------

    BOB-MILER DEP GOOD 9999

    BIL-GATES DEP2 NOGOD 1234

    --------------------------------------------

    I NEED TO COUT THE FIELD [ID] OR FNAME

    HOW MAY TIMES

    THNKS

    ILAN

  • July 31, 2003 at 7:29 am

    #467966

    Here's one problem with your script:

    and Fname LIKE '%%%'

    First, this selects ALL data from Fname.

    Second, it overuses the %.

    Are you trying to get everything from Fname?

    If so, delete that line.

    Are you trying to get Fname where there are only three characters?

    If so, change the theree %'s to three underscores (_):

    and Fname LIKE '___'

    -SQLBill

  • August 1, 2003 at 6:57 am

    #468187

    In addition to SQLBill's observation you are also grouping by mor columns than you are selecting. Try this

    SELECT fname, mhlka, sog, COUNT([id]) AS Count_id

    FROM dbo.searchall

    WHERE fld2 = 2

    AND mhlka_id = 2

    AND fld1 BETWEEN '01/01/2002' AND '01/01/2009' 

    GROUP BY Fname, mhlka, sog

    Far away is close at hand in the images of elsewhere.
    Anon.

  • August 1, 2003 at 7:23 am

    #468196

    quote:

    In addition to SQLBill's observation you are also grouping by mor columns than you are selecting. Try this

    SELECT fname, mhlka, sog, COUNT([id]) AS Count_id

    FROM dbo.searchall

    WHERE fld2 = 2

    AND mhlka_id = 2

    AND fld1 BETWEEN '01/01/2002' AND '01/01/2009' 

    GROUP BY Fname, mhlka, sog

    For saftey here

    fld1 BETWEEN '01/01/2002' AND '01/01/2009'

    do either

    fld1 BETWEEN '20020101' AND '20090101'

    or

    fld1 BETWEEN cast('01/01/2002' as datetime) AND cast('01/01/2009' as datetime)

    the reason is if the implicit does a char compare then it can pick up extraneous data.

  • August 1, 2003 at 7:41 am

    #468205

    Well spotted there James. Hoisted by my own petard. I just copied,edit and pasted. I personally don't use BETWEEN and I always match dates in yyyy-mm-dd format.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply