0

I am trying to do a count and a from on an aliased subquery, but can't figure out how to add the filtering portion:

SELECT  webpages.*, (select count(*) 
                     from webpage_opportunities 
                     where webpage_opportunities.webpage_id = webpages.id) as test_count 
FROM "webpages" 
WHERE (webpages.clicks IS NOT NULL) AND "webpages"."website_id" = 144 AND test_count = 0
GROUP BY webpages.id ORDER BY "webpages"."clicks" DESC LIMIT 50 OFFSET 0

The test_count column shows up in the query results when I remove the AND test_count = 0 but once I add it, the query fails. I also need to make sure the subquery connects to the webpages table, as stated in thje join above.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
jnarowski
  • 113
  • 3
  • Possible duplicate of [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) – Juan Carlos Oropeza Jul 30 '19 at 18:13

1 Answers1

0
SELECT  w.*, COUNT(wo.id) -- but will be 0 because the HAVING
FROM webpages w
LEFT JOIN webpage_opportunities wo
  ON w.id = wo.webpage_id
WHERE (w.clicks IS NOT NULL) 
  AND w."website_id" = 144
GROUP BY w.id 
HAVING COUNT(wo.id) = 0
ORDER BY w."clicks" DESC 
       , COUNT(wo.id) -- optional
LIMIT 50 OFFSET 0
jnarowski
  • 113
  • 3
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • There was a minor typo but this might do it! ` SELECT w.*, (select count(*) from webpage_opportunities where webpage_opportunities.webpage_id = w.id) as test_count FROM webpages w LEFT JOIN webpage_opportunities wo ON w.id = wo.webpage_id WHERE (w.clicks IS NOT NULL) AND w."website_id" = 144 GROUP BY w.id HAVING COUNT(wo.id) = 0 ORDER BY w."clicks" DESC LIMIT 50 OFFSET 0 ` – jnarowski Jul 30 '19 at 18:36
  • I didnt think you need it because you filter by `test_count = 0`? – Juan Carlos Oropeza Jul 30 '19 at 18:37
  • I add the `COUNT()` to the select – Juan Carlos Oropeza Jul 30 '19 at 18:38
  • We might do something like `HAVING COUNT(wo.id) > 5` then we want to display and sort by the test count. – jnarowski Jul 30 '19 at 18:38
  • I'm not sure what was the typo? But you don't need a sub query to calculate the `COUNT` – Juan Carlos Oropeza Jul 30 '19 at 18:39
  • Thanks, this is definitely progress. Can I also sort by the count as well? ORDER BY total_count DESC – jnarowski Jul 30 '19 at 18:40
  • Great! Is there any way to alias the COUNT(wo.id) throughout so it doesn't have to get repeated throughout? – jnarowski Jul 30 '19 at 18:43
  • Did you see the duplicate question link? Or check [here](https://stackoverflow.com/questions/2617661/whats-the-execute-order-of-the-different-parts-of-a-sql-select-statement) to understand the order how SQL process the query. As you can see, the `SELECT` is on the last steps, so you don't have an alias until then. If you have an alias you can use it on the `ORDER BY` but not before – Juan Carlos Oropeza Jul 30 '19 at 18:47