CODEWITHSUNDEEP
sqldatabasepostgresql
Pat
Pat

Reputation: 235

Get row from one table, plus COUNT from a related table

I'm trying to build an SQL query where I grab one table's information (WHERE shops.shop_domain = X) along with the COUNT of the customers table WHERE customers.product_id = 4242451.

The shops table DOES NOT have product.id in it, but the customers table DOES HAVE the shop_domain in it, hence my attempt to do some sort of join.

I essentially want to return the following:

Here is my not so lovely attempt at the query. I think I have the idea right (maybe...) but I can't wrap my head around building this query.

SELECT shops.id, shops.name, shops.shop_domain, COUNT(customers.customer_id) 
FROM shops 
LEFT JOIN customers ON shops.shop_domain = customers.shop_domain 
WHERE shops.shop_domain = 'myshop.com' AND 
      customers.product_id = '4242451' 
GROUP BY shops.shop_id

Relevant database schemas:

shops:
id, name, shop_domain

customers: 
id, name, product_id, shop_domain

Upvotes: 1

Views: 142

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

A correlated subquery should be substantially cheaper (and simpler) for the purpose:

SELECT id, name, shop_domain
     , (SELECT count(*)
        FROM   customers
        WHERE  shop_domain = s.shop_domain
        AND    product_id = 4242451) AS special_count
FROM   shops s
WHERE  shop_domain = 'myshop.com';

This way you only need to aggregate in the subquery, and need not worry about undesired effects on the outer query.

Assuming product_id is a numeric data type, so I use a numeric literal (4242451) instead of a string literal '4242451' - which might cause problems otherwise.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You are close. The condition on customers needs to go in the ON clause, because this is a LEFT JOIN and customers is the second table:

SELECT s.id, s.name, s.shop_domain, COUNT(c.customer_id)
FROM shops s LEFT JOIN
     customers c
     ON s.shop_domain = c.shop_domain AND c.product_id = '4242451'
WHERE s.shop_domain = 'myshop.com'
GROUP BY s.id, s.name, s.shop_domain;

I am also inclined to include all three columns in the GROUP BY, although Postgres (and ANSI/ISO standards) are happy with just id if it is declared as the primary key in the table.

Upvotes: 1

Related Questions