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

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