Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyIgA-0005uN-4C for pgsql-novice@arkaria.postgresql.org; Mon, 06 Jun 2022 19:46:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyIg7-0008V7-KQ for pgsql-novice@arkaria.postgresql.org; Mon, 06 Jun 2022 19:46:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyIg7-0008Uy-7I for pgsql-novice@lists.postgresql.org; Mon, 06 Jun 2022 19:46:19 +0000 Received: from smtp01.smtpout.orange.fr ([80.12.242.123] helo=smtp.smtpout.orange.fr) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nyIg3-00020k-LP for pgsql-novice@lists.postgresql.org; Mon, 06 Jun 2022 19:46:18 +0000 Received: from [192.168.1.14] ([90.42.134.249]) by smtp.orange.fr with ESMTPA id yIfynwZ59tZ0fyIfznHtMs; Mon, 06 Jun 2022 21:46:11 +0200 X-ME-Helo: [192.168.1.14] X-ME-Auth: MzhjNTIwYzFkMiMxMmZhZTU2ZTZlYzljMWMyNTZmMiNiOWE4ZGRjYihkOGY= X-ME-Date: Mon, 06 Jun 2022 21:46:11 +0200 X-ME-IP: 90.42.134.249 Content-Type: multipart/alternative; boundary="------------4SNTRYnodPMDJSeGHf33zW0N" Message-ID: Date: Mon, 6 Jun 2022 21:46:12 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Thunderbird/91.8.0 Reply-To: mauricejea@numericable.fr Subject: Re: SQL question, TOP 5 and all OTHERS Content-Language: fr To: pgsql-novice@lists.postgresql.org References: <6D4DD2B0-E735-459E-BE8C-1D63646C5E32@simplelegal.com> From: Jean MAURICE In-Reply-To: <6D4DD2B0-E735-459E-BE8C-1D63646C5E32@simplelegal.com> X-Antivirus: AVG (VPS 220606-2, 6/6/2022), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------4SNTRYnodPMDJSeGHf33zW0N Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------4SNTRYnodPMDJSeGHf33zW0N Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

Garanti sans virus. www.avg.com
--------------4SNTRYnodPMDJSeGHf33zW0N--