Using COUNT in nested SELECTS

Question

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

Any chance you can expand upon this? I've been poking around online to see if I could figure out how to apply this to my query, as I'd like to speed it up, but haven't come up with anything concrete. — dsclough
– dsclough, Commented Sep 29, 2015 at 20:44

Emacs User · Accepted Answer · 2015-09-24 18:03:25Z

1

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)

answered Sep 24, 2015 at 18:03

Emacs User

1,4951 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dsclough Over a year ago

This worked perfectly. I knew I was close. Thanks a lot.

Greg Viers · Accepted Answer · 2015-09-24 17:56:18Z

1

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

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

answered Sep 24, 2015 at 17:56

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Comments

Hogan · Accepted Answer · 2015-09-29 23:05:47Z

1

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

answered Sep 29, 2015 at 23:05

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

2 Comments

dsclough Over a year ago

This worked a treat. Went from 25-30 second run times to about 5. You are literally god.

Hogan Over a year ago

@dsclough - this solution will also scale better -- if you double the data your old solution will grow at 2x. this at lg x

Collectives™ on Stack Overflow

Using COUNT in nested SELECTS

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related