How to use a results of a SQL Server query in another query

Question

I am fairly new to SQL Server and trying to do something that seems like it ought to be fairly basic, but I must be missing a significant concept.

First, here is the story:

I want to divide the number of line items by the number of orders, thus generating the number of line items on an average order.

I've tried a few options:

I tried joining the two tables together, and then dividing the count() of X by the count of Y. Unfortunately, by X to Y it creates an entry for every Y, so the result of the division is one.
```
select count(x) / count (y) 
from x 
join y
```
I tried making half of the select statement "distinct" by trying to count "distinct" orders but not distinct line items. Turns out you can't make half the select statement be distinct.
```
select count(x) / distinct count (y) 
from x 
join y
```
I tried making a new table, and outputting the sum of the number of orders into the new table. Then I tried to go divide the number of line items by the value of that table. That doesn't work unless I join the tables together, which returns me to the original problem.
```
select count(x) / temptable
from x
join temptable
```
I tried looking up how to output a SQL statement to a variable so I could then use that variable for the division, but got nowhere on that.
I have read diligently through lots of stack overflow posts that hint at this issue but don't actually resolve it.

Any help will be sincerely appreciated!

Yes, I tried using avg but my understanding is that AVG will just average the one column and I need to divide the one sum by the other. — Zac Cramer
– Zac Cramer, Commented Nov 8, 2012 at 22:56

PinnyM · Accepted Answer · 2012-11-08 22:50:59Z

2

SELECT AVG(1.0 * line_item_count) 
FROM (
  SELECT order_id, COUNT(*) as line_item_count
  FROM line_items
  GROUP BY order_id
) line_item_counts

answered Nov 8, 2012 at 22:50

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

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

4 Comments

Please modify this to be avg(1.0*line_item_count). Almost no one wants integer averages, although that is what MSSQL does by default.

oh! i didn't realize you can "nest" select statements, let me give that a shot, thanks.

@ZacCramer: the technical term for this is a "derived table"

And as @GordonLinoff posted, it isn't entirely necessary in this case. But it's certainly good to know!

Gordon Linoff · Accepted Answer · 2012-11-08 22:54:39Z

1

The easiest way to do this is without a subquery:

select count(*) * 1.0 / count(distinct order_id)
from line_items

This assumes that all orders have at least one line item. If this is not the case, you can do:

select count(*) * 1.0 / (select count(*) from orders)
from line_items

Or, if you don't like subqueries in select clauses, you can use a join instead:

select count(li.line_item_id)*1.0/count(distinct o.order_id)
from orders o left outer join
     line_items li
     on o.order_id = li.order_id

answered Nov 8, 2012 at 22:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

oh, this works too! Thanks, I learned new things from both answers.