using group by and count together.

Question

Post reply

using group by and count together.

Viewing 6 posts - 16 through 20 (of 20 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Oops...that's what happens when you don't test. Didn't have SQL Server handy yesterday. Try this:

SELECT COUNT(*)

FROM (

SELECT 1 AS aMatchingRow

FROM tblHhiQuotes

WHERE sGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN

'01 Nov 2010' AND '30 Nov 2010'

AND cTransType = 'FQ'

GROUP BY vSurname

,cPostcode

,cCoverType

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SwePeso (10/29/2010)
SELECTSUM(1) AS Items FROMdbo.tblHhiQuotes WHERE sGlDate <= '28 Oct 2010' AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010' AND cTransType = 'FQ' GROUP BYvSurname, cPostcode, cCoverType WITH ROLLUP HAVINGGROUPING(vSurname) = 1

This looks like the right way to do it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BenWard SSCertifiable Points: 5903 More actions · Answer 3

Chris Morris-439714 (10/29/2010)
SwePeso (10/29/2010)
SELECTSUM(1) AS Items FROMdbo.tblHhiQuotes WHERE sGlDate <= '28 Oct 2010' AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010' AND cTransType = 'FQ' GROUP BYvSurname, cPostcode, cCoverType WITH ROLLUP HAVINGGROUPING(vSurname) = 1
This looks like the right way to do it.

Hmmm... that gives a different number of rows!!!

Chris, your version is fractionally slower by by about 5 ms

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Another method

SELECT TOP(1)COUNT(*) OVER () AS Items

FROMdbo.tblHhiQuotes

WHEREsGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010'

AND cTransType = 'FQ'

GROUP BYvSurname,

cPostcode,

cCoverType

N 56°04'39.16"
E 12°55'05.25"

BenWard SSCertifiable Points: 5903 More actions · Answer 5

interesting...

this returns the correct number of rows but takes twice as much time as my original query.

funny how queries work differently in different circumstances/tables eh!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 6

It really is fascinating sometimes. Often do a double-take at what the optimizer is actually doing to satisfy requests.

Scott Pletcher, SQL Server MVP 2008-2010