CODEWITHSUNDEEP
sqlsql-server
Zac Cramer
Zac Cramer

Reputation: 131

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

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:

  1. 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

  2. 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

  3. 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

  4. 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.

  5. 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!

Upvotes: 1

Views: 5338

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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

Upvotes: 1

PinnyM
PinnyM

Reputation: 35533

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

Upvotes: 2

Related Questions