Really confused newbie - Perhaps a Temp Table query?

SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
ERTutAccounts.Portfolio
,ERTutPositions.PortfolioID;

SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID

CREATE TABLE #ERTutAccounts
(PortfolioID INT IDENTITY(1,1), Portfolio VARCHAR(10))

INSERT INTO #ERTutAccounts
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

CREATE TABLE #ERTutPositions
(PortfolioID INT, MarketValue MONEY, SecID INT)

INSERT INTO #ERTutPositions
SELECT 1, 50000, 1 UNION ALL SELECT 1, 100000, 2 UNION ALL SELECT 1, 150000, 3
UNION ALL SELECT 2, 30000, 1 UNION ALL SELECT 2, 175000, 2 UNION ALL SELECT 2, 250000, 3
UNION ALL SELECT 3, 40000, 1 UNION ALL SELECT 3, 150000, 2 UNION ALL SELECT 3, 250000, 3

SELECT
A.Portfolio
,A.PortfolioID
,SUM(B.MarketValue) AS SumOfMV
,COUNT(B.SecID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;

SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID

DECLARE @NumPortfolios INT = 100000
-- Performance test (add some rows)
;WITH Tally (n) AS (
SELECT TOP (@NumPortfolios) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutAccounts
SELECT Portfolio + CAST(n AS VARCHAR(10))
FROM #ERTutAccounts
CROSS APPLY Tally

;WITH Tally (n) AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutPositions
SELECT 1 + ABS(CHECKSUM(NEWID())) % (3*@NumPortfolios+3)
,20000 + ABS(CHECKSUM(NEWID())) % 250000
,1 + ABS(CHECKSUM(NEWID())) % 500
FROM Tally

DECLARE @Holder1 VARCHAR(10)
,@Holder2 INT
,@Holder3 MONEY
,@Holder4 INT

PRINT 'GROUP BY'
SET STATISTICS TIME ON
SELECT
@Holder1=A.Portfolio
,@Holder2=A.PortfolioID
,@Holder3=SUM(B.MarketValue) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
GROUP BY
A.Portfolio
,A.PortfolioID;
SET STATISTICS TIME OFF

PRINT 'WINDOW AGGREGATE W-DISTINCT'
SET STATISTICS TIME ON
SELECT DISTINCT
@Holder1=A.Portfolio
,@Holder2=B.PortfolioID
,@Holder3=SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS SumOfMV
,@Holder4=COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS [# of Securities]
FROM
#ERTutAccounts A
INNER JOIN #ERTutPositions B
ON A.PortfolioID = B.PortfolioID
SET STATISTICS TIME OFF

DROP TABLE #ERTutAccounts
DROP TABLE #ERTutPositions

dwain.c (3/5/2013)

Steven Willis (3/5/2013)

Another old habit to break if you are using SQL2008 or greater is to avoid GROUP BY when all you really need is an aggregated column value.


SELECT
    A.Portfolio
   ,B.PortfolioID
   ,SUM(B.MarketValue) AS SumOfMV
   ,COUNT(B.SecID) AS [# of Securities]
FROM
    ERTutAccounts A
INNER JOIN ERTutPositions B
    ON A.PortfolioID = B.PortfolioID
GROUP BY
    ERTutAccounts.Portfolio
   ,ERTutPositions.PortfolioID;


SELECT
    A.Portfolio
   ,B.PortfolioID
   ,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
   ,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
    ERTutAccounts A
INNER JOIN ERTutPositions B
    ON A.PortfolioID = B.PortfolioID

You can still use GROUP BY of course if you need to group the portfolios themselves but just to do sums or counts (or min or max, etc) the OVER() clause makes things a lot easier.

Steven,

Your first query needed a little correction (namely to the stuff in bold) to make it run (minor).

But my big question is why your are making the suggestion to unlearn an old habit here when the two queries produce different results. Knocking up some test data:

CREATE TABLE #ERTutAccounts
    (PortfolioID INT IDENTITY(1,1), Portfolio VARCHAR(10)) 

INSERT INTO #ERTutAccounts
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

CREATE TABLE #ERTutPositions
    (PortfolioID INT, MarketValue MONEY, SecID INT) 

INSERT INTO #ERTutPositions
SELECT 1, 50000, 1 UNION ALL SELECT 1, 100000, 2 UNION ALL SELECT 1, 150000, 3
UNION ALL SELECT 2, 30000, 1 UNION ALL SELECT 2, 175000, 2 UNION ALL SELECT 2, 250000, 3
UNION ALL SELECT 3, 40000, 1 UNION ALL SELECT 3, 150000, 2 UNION ALL SELECT 3, 250000, 3

SELECT
    A.Portfolio
   ,A.PortfolioID
   ,SUM(B.MarketValue) AS SumOfMV
   ,COUNT(B.SecID) AS [# of Securities]
FROM
    #ERTutAccounts A
INNER JOIN #ERTutPositions B
    ON A.PortfolioID = B.PortfolioID
GROUP BY
    A.Portfolio
   ,A.PortfolioID;

SELECT
    A.Portfolio
   ,B.PortfolioID
   ,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
   ,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
    #ERTutAccounts A
INNER JOIN #ERTutPositions B
    ON A.PortfolioID = B.PortfolioID

DECLARE @NumPortfolios INT = 100000
-- Performance test (add some rows)
;WITH Tally (n) AS (
    SELECT TOP (@NumPortfolios) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutAccounts
SELECT Portfolio + CAST(n AS VARCHAR(10))
FROM #ERTutAccounts
CROSS APPLY Tally

;WITH Tally (n) AS (
    SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #ERTutPositions
SELECT 1 + ABS(CHECKSUM(NEWID())) % (3*@NumPortfolios+3)
    ,20000 + ABS(CHECKSUM(NEWID())) % 250000
    ,1 + ABS(CHECKSUM(NEWID())) % 500
FROM Tally

DECLARE @Holder1 VARCHAR(10)
    ,@Holder2 INT
    ,@Holder3 MONEY
    ,@Holder4 INT

PRINT 'GROUP BY'
SET STATISTICS TIME ON
SELECT
    @Holder1=A.Portfolio
   ,@Holder2=A.PortfolioID
   ,@Holder3=SUM(B.MarketValue) -- AS SumOfMV
   ,@Holder4=COUNT(B.SecID) -- AS [# of Securities]
FROM
    #ERTutAccounts A
INNER JOIN #ERTutPositions B
    ON A.PortfolioID = B.PortfolioID
GROUP BY
    A.Portfolio
   ,A.PortfolioID;
SET STATISTICS TIME OFF

PRINT 'WINDOW AGGREGATE W-DISTINCT'
SET STATISTICS TIME ON
SELECT DISTINCT
    @Holder1=A.Portfolio
   ,@Holder2=B.PortfolioID
   ,@Holder3=SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS SumOfMV
   ,@Holder4=COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) -- AS [# of Securities]
FROM
    #ERTutAccounts A
INNER JOIN #ERTutPositions B
    ON A.PortfolioID = B.PortfolioID
SET STATISTICS TIME OFF

DROP TABLE #ERTutAccounts
DROP TABLE #ERTutPositions

I'm thinking that the first result is what the OP was looking for. To make them identical, you'd need to change the second query to SELECT DISTINCT. Or you could do as you suggest which is to add GROUP BY, in which case you'd need to aggregate (using MAX or MIN) the window aggregates, but I don't think you can do that (at least not in this case).

My main intention by looking at this was that I have heard that some of the window aggregates don't perform as well as the corresponding GROUP BY notation (and I believe I've seen it proven at least once). So I thought I'd give that a try (also in the SQL above). The results I got were:

GROUP BY
 SQL Server Execution Times:
   CPU time = 483 ms,  elapsed time = 217 ms.

WINDOW AGGREGATE W-DISTINCT
 SQL Server Execution Times:
   CPU time = 2151 ms,  elapsed time = 774 ms

Which arguably might be at least partially due to adding DISTINCT, or maybe not. Whatever the reason, I think they're sufficiently different to take notice.

Edit: Oooh! Just noticed another case of parallelism introduced by SQL Server into a query for my study on parallelism!