• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Using Aggregate columns in a calculation

I'm using SQL Server 2005 and I'm getting the error Invalid column name

SELECT
      UserID,
      COUNT(UserID) OVER() AS UserCount,
      COUNT(CashID) OVER() AS TotalCount,      
      UserCount / TotalCount As UserPctg
FROM
      PopulationBondsCash

I know UserCount and TotalCount don't actually exist until the code finishes but I don't know how to overcome it.  

Thanks
0
evanburen
Asked:
evanburen
  • 4
  • 2
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
SELECT UserID, UserCount, TotalCount, UserCount / TotalCount As UserPctg
FROM
(
SELECT
      UserID,
      COUNT(UserID) OVER() AS UserCount,
      COUNT(CashID) OVER() AS TotalCount      
FROM
      PopulationBondsCash
) A

You cannot use alias in the same query.
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
You need to use GROUP BY
SELECT UserID, UserCount, TotalCount, UserCount / TotalCount As UserPctg
FROM
(
SELECT 
      UserID,
      COUNT(UserID) AS UserCount,
      COUNT(CashID) AS TotalCount      
FROM 
      PopulationBondsCash
GROUP BY UserID
) A

Open in new window


Raj
0
 
evanburenAuthor Commented:
Thanks for both responses but neither is working quite right.  It should be UserCount/TotalCount.  TotalCount = 13338.  Please see attached.
question.png
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ralmadaCommented:
SELECT UserID, UserCount, TotalCount, UserCount / TotalCount As UserPctg
FROM
(
SELECT
      UserID,
      COUNT(UserID) OVER (Partition by UserID) AS UserCount,
      select COUNT() OVER () AS TotalCount      
FROM
      PopulationBondsCash
) A
0
 
ralmadaCommented:
oops

SELECT distinct UserID, UserCount, TotalCount, UserCount / TotalCount As UserPctg
FROM
(
SELECT  
      UserID,
      COUNT(UserID) OVER (Partition by UserID) AS UserCount,
      COUNT() OVER () AS TotalCount      
FROM
      PopulationBondsCash
) A
0
 
ralmadaCommented:
or

SELECT UserID, UserCount, TotalCount, UserCount / TotalCount As UserPctg
FROM
(
SELECT
      UserID,
      COUNT(UserID) UserCount,
      (Select COUNT() from PopulationBondsCash) AS TotalCount      
FROM
      PopulationBondsCash
GROUP BY UserID
) A
0
 
ralmadaCommented:
or to make it shorter

SELECT
      UserID,
      COUNT(UserID) UserCount,
      COUNT(UserID) * 1.0 / (Select COUNT(*) from PopulationBondsCash) AS TotalCount      
FROM
      PopulationBondsCash
GROUP BY UserID
0
 
evanburenAuthor Commented:
Thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now