Re: SQL question, TOP 5 and all OTHERS

Hi Scott,
what about using a Common Table Expression and the clause WITH ?
I am not at home now but you can write something like

WITH top5 AS (SELECT vendor_name AS vendor_name,

       count(DISTINCT inv_id) AS "# of Invoices"

FROM SpendTable

GROUP BY vendor_name

ORDER BY "# of Invoices" DESC

LIMIT 5)
SELECT * FROM top5
UNION

SELECT 'all other' AS vendor_name,

       count(DISTINCT st.inv_id) AS "# of Invoices"

FROM SpendTable AS st

WHERE st.vendor_name NOT IN (SELECT vendor_name FROM top5)

ORDER BY "# of Invoices" DESC

Best regards,
--
Jean MAURICE
Grenoble - France - Europe
www.j-maurice.fr
www.atoutfox.org
www.aedtf.org

Le 06/06/2022 à 19:22, Scott Holliday a écrit :
>
> Hi,
>
> I’m trying to get up-to-speed with PostgreSQL and have a dumb question. I have
> a basic query to pull the top 5 vendors that have sent me the most bills. I
> would like to lump all the other vendors into a row named “Other” and get a
> count of all those bills excluding the top 5. Below is the basic query.
>
> SELECT vendor_name AS vendor_name,
>
>        count(DISTINCT inv_id) AS "# of Invoices"
>
> FROM SpendTable
>
> GROUP BY vendor_name
>
> ORDER BY "# of Invoices" DESC
>
> LIMIT 5
>
> Thanks,
>
> Scott
>

--
J. MAURICE

--
Cet email a fait l'objet d'une analyse antivirus par AVG.
http://www.avg.com