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)

SELECT SUM(1) AS Items FROM dbo.tblHhiQuotes WHERE sGlDate <= '28 Oct 2010' AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010' AND cTransType = 'FQ' GROUP BY vSurname, cPostcode, cCoverType WITH ROLLUP HAVING GROUPING(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)

SELECT SUM(1) AS Items FROM dbo.tblHhiQuotes WHERE sGlDate <= '28 Oct 2010' AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010' AND cTransType = 'FQ' GROUP BY vSurname, cPostcode, cCoverType WITH ROLLUP HAVING GROUPING(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: 39725 More actions · Answer 4

SwePeso

SSC-Dedicated

Points: 39725

October 29, 2010 at 7:35 am

#1244178

Another method



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

FROM		dbo.tblHhiQuotes

WHERE		sGlDate <= '28 Oct 2010'

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

		AND cTransType = 'FQ'

GROUP BY	vSurname,

		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

scott.pletcher

SSCertifiable

Points: 5958

October 29, 2010 at 9:04 am

#1244260

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