sql count? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql count?

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-07-08 : 06:48:59

SELECT COUNT(Jobs.JobId) AS occurances, Jobs.JobId, Jobs.JobTitle, JobAttributes.AttId, Attributes.AttMaster

FROM Jobs INNER JOIN JobAttributes ON Jobs.JobId = JobAttributes.JobId
INNER JOIN Attributes ON JobAttributes.AttId = Attributes.AttId

WHERE Attributes.AttMaster=404

GROUP BY Jobs.JobId, Jobs.JobTitle, Attributes.AttMaster, JobAttributes.AttId

ORDER BY occurances DESC

why is the above query returning only 1 for occurances when there is loads of repeating JobId's in the results I see?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-08 : 06:55:06

The combination "Jobs.JobId, Jobs.JobTitle, JobAttributes.AttId, Attributes.AttMaster" may occurs once!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

iradev
Starting Member

45 Posts

Posted - 2010-07-08 : 07:01:46

so is there another way of approaching my problem?

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 07:20:38

Maybe this


SELECT COUNT(Jobs.JobId)over(partition by Jobs.JobId)AS occurances, Jobs.JobId, Jobs.JobTitle, JobAttributes.AttId, Attributes.AttMaster

FROM Jobs INNER JOIN JobAttributes ON Jobs.JobId = JobAttributes.JobId
INNER JOIN Attributes ON JobAttributes.AttId = Attributes.AttId

WHERE Attributes.AttMaster=404 

GROUP BY Jobs.JobId, Jobs.JobTitle, Attributes.AttMaster, JobAttributes.AttId

ORDER BY occurances DESC

If not then please post some sample data.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

iradev
Starting Member

45 Posts

Posted - 2010-07-08 : 07:36:46

Thank you Idera, that works fine. Could you please explain how it worked? I couldn't find much info on 'over' and 'partition by'.

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 07:42:55

You are welcome.
Basically it gets the count of unique sets of JobId and displays the count against each jobid.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

iradev
Starting Member

45 Posts

Posted - 2010-07-08 : 07:48:38

Thanks, learnt something valuable :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources