Subject: SQL question, TOP 5 and all OTHERS

From: Scott Holliday <[email-masked]>
Date: Mon, 6 Jun 2022 17:22:02 +0000

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

---

From: Jean MAURICE <[email-masked]>
Date: Mon, 6 Jun 2022 21:46:12 +0200

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,

FROM SpendTable

GROUP BY vendor_name

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)

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.
>

-- 
J. MAURICE

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

---

From: Skylar Thompson <[email-masked]>
Date: Mon, 6 Jun 2022 14:12:13 -0700

On Mon, Jun 06, 2022 at 09:46:12PM +0200, Jean MAURICE wrote:
> ?????? count(DISTINCT inv_id) AS "# of Invoices"
> 
> FROM SpendTable
> 
> GROUP BY vendor_name
> 
> ?????? count(DISTINCT st.inv_id) AS "# of Invoices"
> 
> FROM SpendTable AS st
> 

There might be a challenge with ties, especially if you don't order by the
vendor name since you could get different results even on the same data
set, depending on how the query plan goes. It depends on what the OP is
looking for, I guess.

-- 
-- Skylar Thompson ([email-masked])
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His