need help with COUNT

Question

need help with COUNT

midan1

SSCertifiable

Points: 5100
More actions
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

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

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

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

midan1 SSCertifiable Points: 5100 More actions · Answer 2

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

SQLBill SSC Guru Points: 51440 More actions · Answer 3

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

David Burrows SSC Guru Points: 65143 More actions · Answer 4

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

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.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

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.