Postgres get number of rows from query

Hi guys, I am this query

WITH city_title AS (
    SELECT
        "geo"."city",
        "jt".title
    FROM
        "jobposition" "jp"
    LEFT JOIN "geo_location" "geo" ON "geo"."id" = "jp"."geoLocationId"
    LEFT JOIN "jp_jobtitles" "jp_jt" ON "jp_jt"."jobpositionId" = "jp".id
    LEFT JOIN "jobtitle" "jt" ON "jt".id = "jp_jt"."jobtitleId"
WHERE
    "jp"."geoLocationId" IS NOT NULL
)
SELECT
    city, title
FROM
    city_title
GROUP BY
    city,
    title

WITH city_title AS (
    SELECT
        "geo"."city",
        "jt".title
    FROM
        "jobposition" "jp"
    LEFT JOIN "geo_location" "geo" ON "geo"."id" = "jp"."geoLocationId"
    LEFT JOIN "jp_jobtitles" "jp_jt" ON "jp_jt"."jobpositionId" = "jp".id
    LEFT JOIN "jobtitle" "jt" ON "jt".id = "jp_jt"."jobtitleId"
WHERE
    "jp"."geoLocationId" IS NOT NULL
)
SELECT
    city, title
FROM
    city_title
GROUP BY
    city,
    title

Which returns ~10000 rows. Is there a way to modify this query to just return the number of rows and maybe make the query faster?

15 Replies

vrba•2y ago

speed wise, if you just need the count there is no need to use

GROUP BY

GROUP BY

utdev•2y ago

true, removed that

Matvey•2y ago

Won't this just return the number of rows?

SELECT COUNT(*) FROM "jobposition" "jp" WHERE "jp"."geoLocationId" IS NOT NULL

SELECT COUNT(*) FROM "jobposition" "jp" WHERE "jp"."geoLocationId" IS NOT NULL

vrba•2y ago

if not there, then insted of

SELECT
    city, title

SELECT
    city, title

USE

SELECT COUNT(*)

SELECT COUNT(*)

utdev•2y ago

That does not return the same number of rows 🤔

utdev•2y ago

That returns this 😕

Matvey•2y ago

What about this one?

SELECT COUNT(DISTINCT "geoLocationId") FROM "jobposition"

SELECT COUNT(DISTINCT "geoLocationId") FROM "jobposition"

utdev•2y ago

That returns ~15000, should be ~10000 rows 😄

vrba•2y ago

try

COUNT(DISTINCT city, title)

COUNT(DISTINCT city, title)

utdev•2y ago

yeah tried that aswell just now

SELECT
    count(DISTINCT(city, title))
FROM
    city_title

SELECT
    count(DISTINCT(city, title))
FROM
    city_title

Seems to work thanks guys

vrba•2y ago

no problem glad to help 🙂

utdev•2y ago

The query should be fast right? 😄

vrba•2y ago

if you removed the group by yes

utdev•2y ago

nice thanks!

Matvey•2y ago

😃 never noticed there are 3 different smile emojis

Gaming

Programming

Postgres get number of rows from query