Did I invent new feature for Count(*) over (Partition by 1) ?

Question

Post reply

Did I invent new feature for Count(*) over (Partition by 1) ?

mario17

SSCertifiable

Points: 6079
More actions
June 9, 2014 at 2:03 pm

#287247

Hi, all
I was faced with one problem and could not find any solution in any textbook, so played and SOLVED it !!!!!
This is about that TotalCountries, I put in all rows to make it easy for my RDL.
/*
SELECT * INTO #tt FROM (
SELECT 1111 CustID, 1000 Profit, 'USA' Country UNION
SELECT 1112 CustID, 1000 Profit, 'USA' Country UNION
SELECT 1113 CustID, 1000 Profit, 'CAN' Country UNION
SELECT 1114 CustID, 1000 Profit, 'CAN' Country UNION
SELECT 1114 CustID, 1000 Profit, 'MEX' Country UNION
SELECT 1117 CustID, 1000 Profit, 'GBR' Country UNION
SELECT 1116 CustID, 1000 Profit, 'USA' Country ) b
*/ --SELECT * FROM #tt -- DROP TABLE #tt
SELECT
Country ,
MAX(CustID) CustID,
SUM(Profit) Profit,
COUNT(*) OVER (PARTITION BY 1 ) AS TotalCountries
FROM #tt
GROUP BY Country

Viewing 9 posts - 1 through 8 (of 8 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Can you tell us what you were trying to solve with this?

I note you get the same output with this:

SELECT

Country ,

MAX(CustID) CustID,

SUM(Profit) Profit,

COUNT(*) OVER (PARTITION BY 99 ) AS TotalCountries

FROM #tt

GROUP BY Country

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

TheSQLGuru (6/9/2014)
Can you tell us what you were trying to solve with this?
I note you get the same output with this:
SELECT
Country ,
MAX(CustID) CustID,
SUM(Profit) Profit,
COUNT(*) OVER (PARTITION BY 99 ) AS TotalCountries
FROM #tt
GROUP BY Country

This will work for ANY constant or deterministic function.

You could use '' or even getdate()

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

This will work for ANY constant or deterministic function.

I was hoping the OP would work that out and either figure things out for himself or come back for some details. 😉

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mario17 SSCertifiable Points: 6079 More actions · Answer 4

Tx, Sean and all

Yes, I use 1 as just a litteral, and could be anything.

I'm trying to get COUNT of (DISTINCT Countries), so this value will be the same for all output, i.e. we have 4 different countries in this list.

I just don't want to get inline select thinking that analytical function will perform better.

SELECT

Country ,

MAX(CustID) CustID,

COUNT(*) PerCountry,

COUNT(*) OVER (PARTITION BY 1 ) AS TotalCountries,

(SELECT COUNT (DISTINCT country) FROM #tt) TotalCountries2

FROM #tt

GROUP BY Country

Tx

Mario

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

The windowing function does indeed perform better than the double-hit. I just tested to 100K rows and it was anyway.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 6

Try this next time:

COUNT(*) OVER ()

Eddie Wuerch
MCM: SQL

mario17 SSCertifiable Points: 6079 More actions · Answer 7

mario17

SSCertifiable

Points: 6079

June 10, 2014 at 10:45 am

#1720505

Thanks, Eddie

So that not mine invention;-)

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 8

Eddie Wuerch (6/9/2014)
Try this next time:
COUNT(*) OVER ()

Interesting, good to know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP