GROUP BY, HAVING, and COUNT to get aggregated results

Question

I have seen a few threads about using these three functions but I can't get the results I want based on what I read.

I am using SQL SERVER 2012. The table is APXFirm.AdvApp.vPerformance Here is the table schema

I tried the following query:

USE APXFirm
SELECT PortfolioBaseCode
 ,COUNT(  portfoliobasecode) AS Pcount
  FROM APXFirm.AdvApp.vPerformance
        WHERE rowtypecode = 'd'   
        and DetailKeyCode is NOT NULL
        and NetOrGrossCode = 'n'
and DetailKeyCode <> 'us'
and PortfolioBaseCode IN ('test','test2')
GROUP BY PortfolioBaseCode,
      NetOrGrossCode, 
            RowTypeCode ,
           DetailKeyCode ,
           MarketValue 
 HAVING marketvalue > 1

But this gives me a result like this:

PortfolioBaseCode   Pcount
test                 1
test                 1
test                 1
test2                1
test2                1
test2                1
test2                1

I want a result like this

PortfolioBaseCode   Pcount
test                 3
test2                4

I'm not sure if I need a subquery, a join or to use distinct in the count to achieve this or something else. I did also try this line but it didn't change the result.

 ,COUNT( DISTINCT portfoliobasecode) AS Pcount

Thanks

Answer 1

You have too many columns in the group by. I think you just want:

SELECT PortfolioBaseCode, COUNT(  portfoliobasecode) AS Pcount
FROM APXFirm.AdvApp.vPerformance
WHERE rowtypecode = 'd' AND  
      DetailKeyCode is NOT NULL AND
      NetOrGrossCode = 'n' AND
      DetailKeyCode <> 'us' AND
      PortfolioBaseCode IN ('test', 'test2')
GROUP BY PortfolioBaseCode
HAVING SUM(marketvalue) > 1;

I'm actually not sure what the HAVING clause is supposed to be doing. Perhaps you can remove it. Perhaps you can move the logic marketvalue > 1 to the WHERE clause.

Answer 2

Replace

HAVING marketvalue > 1

with

HAVING Pcount > 1