Re: SQL question, TOP 5 and all OTHERS

       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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

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

Garanti sans virus. www.avg.com