public inbox for [email protected]  

help / color / mirror / Atom feed

SQL question, TOP 5 and all OTHERS
3+ messages / 3 participants
[nested] [flat]

* SQL question, TOP 5 and all OTHERS
@ 2022-06-06 17:22  Scott Holliday <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Scott Holliday @ 2022-06-06 17:22 UTC (permalink / raw)
  To: pgsql-novice

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL question, TOP 5 and all OTHERS
@ 2022-06-06 19:46  Jean MAURICE <[email protected]>
  parent: Scott Holliday <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Jean MAURICE @ 2022-06-06 19:46 UTC (permalink / raw)
  To: [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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL question, TOP 5 and all OTHERS
@ 2022-06-06 21:12  Skylar Thompson <[email protected]>
  parent: Jean MAURICE <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Skylar Thompson @ 2022-06-06 21:12 UTC (permalink / raw)
  To: Jean MAURICE <[email protected]>; +Cc: [email protected]

On Mon, Jun 06, 2022 at 09:46:12PM +0200, Jean MAURICE wrote:
> 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

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 protected])
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2022-06-06 21:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-06 17:22 SQL question, TOP 5 and all OTHERS Scott Holliday <[email protected]>
2022-06-06 19:46 ` Jean MAURICE <[email protected]>
2022-06-06 21:12   ` Skylar Thompson <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox