How to create column that measures the number of occurrences in SQL?-CodePudding

I am trying to count the number of unique occurrences of a certain column - "car_id" For example, if Car_Shop_A has 3 occurrences of a certain car_id I would like this count to be displayed in the table like so: "Shop1 - car_1 - 3"

SELECT
    shop_name,
    car_id,
    Count(car_id) as count
    
FROM
    car_database
WHERE
    date between '20210101' AND '2021131'
    AND shop_name IN ('Shop1',
                        'Shop2',
                        'Shop3',
                        'Shop4',
                        'Shop5',
                        'Shop6')
                        
                                
group by shop_name, car_id
order by count desc

The above is my current query, however it returns this table:

This query returns the same count for all shops and returns the number of rows rather than the number of each car_id occurrence. How can I fix this?

CodePudding user response：

If I've understood you correctly, removing the car_id column from the grouping and adding DISTINCT to the count function should return the result you need:

SELECT
    shop_name,
    Count(distinct car_id) as count
    
FROM
    car_database
WHERE
    date between '20210101' AND '2021131'
    AND shop_name IN ('Shop1',
                        'Shop2',
                        'Shop3',
                        'Shop4',
                        'Shop5',
                        'Shop6')
                        
                                
group by shop_name
order by count desc

CodePudding user response：

SELECT
    shop_name || ' - ' || car_id || ' - ' || count(1) as shop_car_listing
FROM
    car_database
WHERE
    date between '20210101' AND '2021131'
    AND shop_name IN ('Shop1',
                      'Shop2',
                      'Shop3',
                      'Shop4',
                      'Shop5',
                      'Shop6')                              
group by shop_name, car_id 
order by count(1) desc

fiddle