SQL Join to count based on query results

Question

I'm getting hung up trying to count records from one table based upon the results of a query. My table looks like this:

parent_id |  site_id  | description
-----------------------------------
A1           1          FL
A1           2          NJ
A1           A1         Company_A

First I created a query to give me the company name on the same row as the state (description). My initial query is:

SELECT master.description, sites.description, sites.site_id 
FROM sites sites
INNER JOIN sites master 
  ON sites.parent_id = master.site_id

Which results in:

Company_A     FL    1
Company_A     DE    2

So far so good. What I want to do, though, is count the number of orders from the ORDERS table for each site_id, even where the number of orders are 0.

I'm stumped on how to count from the ORDERS table based on the results of my query? Any help on this join would be appreciated.

UPDATE: Almost there! Here's what is working:

SELECT master.description, sites.description, count(order_id) orderCount
FROM sites sites
INNER JOIN sites master
ON sites.parent_id = master.site_id
LEFT JOIN orders o
ON sites.site_id = o.site_id
WHERE sites.site_type = 3
GROUP BY master.description, sites.description, sites.site_id
ORDER BY master.description

The only issue I have left is if I add a date range I get the correct results, but any sites with ZERO orders for that period are not shown. So if I add:

WHERE sites.site_type = 3 AND o.order_date > '2015-10'

I get the correct result but only for sites with orders during that period. What am I missing?

Could you please add the description (or preferably, the DDL) of the orders table? — Mureinik
– Mureinik, Commented Oct 3, 2015 at 20:04

Conrad Frix · Accepted Answer · 2015-10-04 19:00:19Z

1

You can solve this pretty easily with a LEFT Join and then a count on order id. It's important to not count the row count(*) because that would return a value of 1 even when there's no orders

Additionally any filtering on the orders table needs to be done inside the JOIN.

SELECT master.description, sites.description, sites.site_id 
       ,count(order_id) order_count 
FROM sites sites
INNER JOIN sites master 
  ON sites.parent_id = master.site_id
LEFT JOIN orders o
ON master.site_id = o.Site_id
   and   o.order_date > '2015-10'
WHERE
       sites.site_type = 3 
GROUP by
 master.description, sites.description, sites.site_id

You didn't describe your Order table so I'm guessing at the join criteria

edited Oct 4, 2015 at 19:00

answered Oct 3, 2015 at 20:17

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

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

2 Comments

Stephen Dundas Over a year ago

THANK YOU @ConradFrix -- I used your solution and am nearly there. I added a date range, though (see update in original post) and I'm not getting results with zero orders during the period. Any ideas?

Conrad Frix Over a year ago

@StephenDundas Any filtering on the right side of a left join turns the join into an inner join. Just move o.order_date > '2015-10 to the join clause e.g. on ON master.site_id = o.Site_id and o.order_date > '2015-10' and it will fix it

Rafał Wojtaszek · Accepted Answer · 2015-10-03 20:12:43Z

1

This should meet your expectations:

SELECT master.description, sites.description, sites.site_id,
ISNULL((SELECT COUNT(*) FROM Orders o where master.siteId = o.siteId),0) as ordersCount
FROM sites sites
INNER JOIN sites master 
  ON sites.parent_id = master.site_id

edited Oct 3, 2015 at 20:12

answered Oct 3, 2015 at 20:04

Rafał Wojtaszek

6686 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL Join to count based on query results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related