CODEWITHSUNDEEP
sqlpostgresql-9.2
Souvik Ray
Souvik Ray

Reputation: 3028

How to get item count along with total count with a single query in sql?

I have a table that stores information about items sold across all locations by a sweet shop. It sells pastries, waffles, ice creams, candies etc.

I need to get this information from the table.

Which item was sold the most per location along with total items sold in those locations.

Below is the structure of the table

     Column     |            Type             | Modifiers 
----------------+-----------------------------+-----------
 id             | integer                     | 
 date           | timestamp without time zone | 
 item           | character varying(15)       | 
 location       | character varying(25)       |

Now I break it down into two separate questions

  1. Which item was sold the most per location
  2. Total count of items sold per location.

These are my queries:

Query #1:

select location, item, count(item) as count_ 
from sweet_shop 
group by location, item;

Query #2:

select location, count(item) as count_ 
from sweet_shop 
group by location;

As you can see, I make two separate queries and then I need to map these two information to correct locations and get the final output. I am looking for an output like below

  location     |  item             | item_count    total_count 
---------------+-------------------+-------------|----------
 BA2           | candies           |   400       |     550
 BA4           | pastries          |   320       |     610 
 BA3           | waffles           |   250       |     400 
 BA7           | ice creams        |   180       |     500

But I want to get this information in a single query as opposed to making two separate queries. How can this be done?

NOTE: I am using Postgres 9.2

Upvotes: 2

Views: 2339

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Postgres has a very nice extension DISTINCT ON, that allows you to do this without subqueries:

SELECT DISTINCT ON (location) location item,
       COUNT(*) AS item_count,
       SUM(COUNT(*)) OVER (PARTITION BY location) AS total_count
FROM sweet_shop
GROUP BY location, item
ORDER BY location, COUNT(*) DESC;

DISTINCT ON return the first row of a group of rows with the same key, where "first" is defined by the ORDER BY clause.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522049

I would aggregate by location and item, using COUNT() as an analytic function to generate the total count per location. Also, use ROW_NUMBER to identify the top performing item in each location.

WITH cte AS (
    SELECT location, item, COUNT(*) AS item_count,
           SUM(COUNT(*)) OVER (PARTITION BY location) AS total_count,
           ROW_NUMBER() OVER (PARTITION BY location
                              ORDER BY COUNT(*) DESC) rn
    FROM sweet_shop
    GROUP BY location, item
)

SELECT location, item, item_count, total_count
FROM cte
WHERE rn = 1;

Upvotes: 4

Related Questions