public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jean MAURICE <[email protected]>
To: [email protected]
Subject: Re: SQL question, TOP 5 and all OTHERS
Date: Mon, 6 Jun 2022 21:46:12 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
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
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: SQL question, TOP 5 and all OTHERS
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox