CODEWITHSUNDEEP
sqlsql-servercount
dsclough
dsclough

Reputation: 131

Using COUNT in nested SELECTS

I'm trying to count the number of instances of a value in a column and return it in another column. I've successfully done the calculation with a hard coded value but can't figure out how to get a variable from the main SELECT into the COUNT.

In the sample data at the bottom, CommissionRate shows the number of times the DealInstanceOID value in that row shows up in the DealInstanceOID column. My hard coded solution works for all of these values, but getting this to happen dynamically is mystifying me. The DealInstanceOID variable is out of the scope of the nested SELECT and I'm unsure of how to work around that.

I posted this question earlier and had some complaints about how my tables are joined - wasn't able to get any more feedback from those posters and I am reposting as they suggested.

SELECT
D.DealOID
DD.DealInstanceOID
, CommissionRate = (SELECT (DealInstanceOID COUNT(*) FROM dbo.DealDetail WHERE DealInstanceOID = 4530))
, Commission = CONVERT(MONEY,C.Commission,1)

FROM dbo.Book AS B WITH(NOLOCK)
INNER JOIN Contract as C WITH(NOLOCK) ON B.BookOID = C.BookOID
INNER JOIN Deal as D WITH(NOLOCK)ON C.ContractOID = D.ContractOID
INNER JOIN DealInstance DI WITH(NOLOCK) ON DI.DealOID = D.DealOID
INNER JOIN DealDetail AS DD WITH(NOLOCK)ON DD.DealInstanceOID = DI.DealInstanceOID

GROUP BY
DD.DealInstanceOID
, D.DealOID 
, C.Commission
, B.BookOID

ORDER BY DealOID ASC


DealOID |Commission |CommissionRate|Commission/Rate|DealInstanceOID
101     |  $1000    |       5      |     $200.00   |     4530
101     |  $1000    |       5      |     $200.00   |     4530
101     |  $1000    |       5      |     $200.00   |     4530
101     |  $1000    |       5      |     $200.00   |     4530
101     |  $1000    |       5      |     $200.00   |     4530
101     |  $5000    |       6      |     $833.33   |     4531
102     |  $5000    |       6      |     $833.33   |     4531
102     |  $5000    |       6      |     $833.33   |     4531
102     |  $5000    |       6      |     $833.33   |     4531
102     |  $5000    |       6      |     $833.33   |     4531
102     |  $5000    |       6      |     $833.33   |     4531
103     |  $6000    |       3      |     $2,000.00 |     4540
103     |  $6000    |       3      |     $2,000.00 |     4540
103     |  $6000    |       3      |     $2,000.00 |     4540

Upvotes: 0

Views: 145

Answers (3)

Hogan
Hogan

Reputation: 70523

Using a join instead of a select for every row.

The key is to use a CTE. As you can see below the result is two selects against the database as opposed to one for every row.

WITH counts AS
(  -- This will give a virtual table (CTE) with ID and count
   SELECT DealInstanceOID, COUNT(*) as C
   FROM dbo.DealDetail
   GROUP BY DealInstanceOID
)
SELECT
  D.DealOID,
  DI.DealInstanceOID,
  counts.C AS CommissionRate,
  CONVERT(MONEY,C.Commission,1) AS Commission 
FROM dbo.Book AS B WITH(NOLOCK)
JOIN Contract as C WITH(NOLOCK) ON B.BookOID = C.BookOID
JOIN Deal as D WITH(NOLOCK)ON C.ContractOID = D.ContractOID
JOIN DealInstance DI WITH(NOLOCK) ON DI.DealOID = D.DealOID
JOIN counts ON DI.DealInstanceOID = counts.DealInstanceOID
GROUP BY DI.DealInstanceOID, D.DealOID, C.Commission, B.BookOID
ORDER BY DealOID ASC

Upvotes: 1

Emacs User
Emacs User

Reputation: 1475

Two problems with your scalar sub-select statement. One is a syntax error and the other is referencing. Fix them as follows:

CommissionRate = (SELECT COUNT(*) FROM dbo.DealDetail as s WHERE s.DealInstanceOID = dd.DealInstanceOID)

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

You should be able to reference it by the table alias and column name:

...
 WHERE dbo.DealDetail.DealInstanceOID = DD.DealInstanceOID))
...

Upvotes: 1

Related Questions