Transact-SQL - Max count with an extra group by

Question

Yesterday I was struggling on my SQL script. And I still don't have the desired solution. I use this SQL script for a report in my SSIS project (datawarehousing).

I have a query with a couple of joins. The tables I use are the following ones.

factsales - fact table with a lot of aggregate (referencing) keys and salesprice of a single car for a single customer by a single salesperson at a single dealer

DimDate - a table with different date notations between 2001 and 2007

dimcar - information about car, cartype and manufacturer name

I want to create a script to display the maximum sold cars on an annual basis (yearly)

The Query I have is something like this

SELECT Count(*) most_cars_sold, 
       Car.carmodeltype, 
       Car.carmodeldetails, 
       Car.manufacturername, 
       dd.year 
FROM   factsales sale 
       INNER JOIN dimdate dd 
               ON dd.date_sid = sale.orderdate_sid 
       LEFT JOIN dimcar car 
              ON car.car_sid = sale.car_sid 
GROUP  BY Car.carmodeltype, 
          Car.carmodeldetails, 
          Car.manufacturername, 
          dd.year

The result is:

6   406     Break HDI 110pk XT  Peugeot 2000
4   Civic   DX 2dr  Honda   2000
4   Octavia Combi TDi 74kW Comf4x4  Skoda   2000
3   Megane  1.5dCi Expressi.Basi.5d Renault 2000
3   Polo    1.9TDI Sportline 5drs   Volkswagen  2000
3   Touran  2.0TDI Trendline    Volkswagen  2000
3   Vectra  2.0DTi Comfort 4drs Opel    2000
3   Corolla D4-D 116 Sol Bus.   Toyota  2000
3   Corolla Verso 2.0D4-D 90 Terra  Toyota  2000
3   406     Break HDI 110pk GT  Peugeot 2000

This example only shows 2000, but the result goes to year 2007

However.. I only want to display "6 406 Break HDI 110pk XT Peugeot 2000"

Then I tried this query:

SELECT Max(most_cars_sold) maxSold, 
       year 
FROM   (SELECT Count(*) most_cars_sold, 
               Car.carmodeltype, 
               Car.carmodeldetails, 
               Car.manufacturername, 
               dd.year 
        FROM   factsales sale 
               INNER JOIN dimdate dd 
                       ON dd.date_sid = sale.orderdate_sid 
               LEFT JOIN dimcar car 
                      ON car.car_sid = sale.car_sid 
        GROUP  BY Car.carmodeltype, 
                  Car.carmodeldetails, 
                  Car.manufacturername, 
                  dd.year) foo 
GROUP  BY year 
ORDER  BY year, 
          maxsold DESC

Result:

That looks fine.. but I need the CarModelType, CarModelDetails, ManufacturerName in the result as well..

Can somebody help?

Sample table data would be fine, and the expected result as well. — jarlh, Jun 20 '16 at 09:55
Possible duplicate of [Selecting first row per group](http://stackoverflow.com/questions/10930347/selecting-first-row-per-group) — Vladimir Baranov, Jun 20 '16 at 11:09

score 0 · Answer 1 · answered Jun 20 '16 at 10:10

try this, if you working with PostgreSQL or Oracle you can right shorter script using windows function

SELECT a.maxSold,
       a.year,
       b.carmodeltype,
       b.carmodeldetails,
       b.manufacturername
FROM (
SELECT Max(most_cars_sold) maxSold, 
       year 
FROM   (SELECT Count(*) most_cars_sold, 
               Car.carmodeltype, 
               Car.carmodeldetails, 
               Car.manufacturername, 
               dd.year 
        FROM   factsales sale 
               INNER JOIN dimdate dd 
                       ON dd.date_sid = sale.orderdate_sid 
               LEFT JOIN dimcar car 
                      ON car.car_sid = sale.car_sid 
        GROUP  BY Car.carmodeltype, 
                  Car.carmodeldetails, 
                  Car.manufacturername, 
                  dd.year) foo 
GROUP  BY year 
ORDER  BY year, 
          maxsold DESC )a
INNER JOIN (SELECT Count(*) most_cars_sold, 
               Car.carmodeltype, 
               Car.carmodeldetails, 
               Car.manufacturername, 
               dd.year 
        FROM   factsales sale 
               INNER JOIN dimdate dd 
                       ON dd.date_sid = sale.orderdate_sid 
               LEFT JOIN dimcar car 
                      ON car.car_sid = sale.car_sid 
        GROUP  BY Car.carmodeltype, 
                  Car.carmodeldetails, 
                  Car.manufacturername, 
                  dd.year) b on b.most_cars_sold=a.maxSold

This did not solve the problem.. not even working, get error messages.. — user3402571, Jun 20 '16 at 15:56

Semen Bekrenev · Answer 2 · 2016-06-21T07:09:51.157

WITH TIES Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows

----------------------------------------------------------------------------
--Test tables start
----------------------------------------------------------------------------
if object_id(N'tempdb..#factsales', N'U') is not null drop table #factsales;
select [orderdate_sid] = 1, [car_sid] = 1
into #factsales
union all select 1, 1
union all select 1, 2
union all select 1, 2
union all select 1, 3
union all select 2, 1
union all select 2, 3
union all select 3, 1
union all select 3, 2
union all select 3, 2;
--select * from #factsales

if object_id(N'tempdb..#dimdate', N'U') is not null drop table #dimdate;
select [date_sid] = 1, [year] = 2000
into #dimdate
union all select 2, 2002
union all select 3, 2003;
--select * from #dimdate

if object_id(N'tempdb..#dimcar', N'U') is not null drop table #dimcar;
select [car_sid] = 1, [carmodeltype] = N'406', [carmodeldetails] = N'Break HDI 110pk XT', [manufacturername] = N'Peugeot'
into #dimcar
union all select 2, N'Civic', N'DX 2dr', N'Peugeot'
union all select 3, N'Octavia', N'Combi TDi 74kW Comf4x4', N'Skoda';
--select * from #dimcar
----------------------------------------------------------------------------
--Test tables end
---------------------------------------------------------------------------- 

select top(1) with ties
        sq.*
from
(
    select [most_cars_sold] = count(*)
           ,car.[carmodeltype]
           ,car.[carmodeldetails]
           ,car.[manufacturername]
           ,dd.[year]
    from #factsales as sale 
    join #dimdate as dd on dd.[date_sid] = sale.[orderdate_sid]
    left join #dimcar as car on car.[car_sid] = sale.[car_sid]
    group  by car.[carmodeltype], car.[carmodeldetails], car.[manufacturername], dd.[year]
) as sq
order by dense_rank() over(partition by sq.[year] order by [most_cars_sold] desc);

WITH TIES Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows. — Semen Bekrenev, Jun 20 '16 at 11:25

score 0 · Answer 3 · answered Jun 20 '16 at 11:02

0

SELECT Max(most_cars_sold) OVER (PARTITION BY Year) maxSold
    ,Year
    ,carmodeltype
    ,carmodeldetails
    ,manufacturername
FROM (
    SELECT Count(*) most_cars_sold
        ,Car.carmodeltype
        ,Car.carmodeldetails
        ,Car.manufacturername
        ,dd.year
    FROM factsales sale
    INNER JOIN dimdate dd ON dd.date_sid = sale.orderdate_sid
    LEFT JOIN dimcar car ON car.car_sid = sale.car_sid
    GROUP BY Car.carmodeltype
        ,Car.carmodeldetails
        ,Car.manufacturername
        ,dd.year
    ) foo
ORDER BY YEAR

answered Jun 20 '16 at 11:02

N.Dinesh.Reddy

522
2
7
15

Use partition by instead of group by it will solve Above case – N.Dinesh.Reddy Jun 20 '16 at 11:05
This did not help as well :( – user3402571 Jun 20 '16 at 15:57

score 0 · Accepted Answer · answered Jun 20 '16 at 18:13

I found my own solution.. a very special approach if you'd ask me.. but the result is promising

SELECT Concat(Cast(Max(most_cars_sold) AS NVARCHAR(255)), year), 
       Max(most_cars_sold) cars_sold, 
       year, 
       carmodeltype, 
       carmodeldetails, 
       manufacturername 
FROM   (SELECT Count(*) most_cars_sold, 
               Car.carmodeltype, 
               Car.carmodeldetails, 
               Car.manufacturername, 
               dd.year 
        FROM   factsales sale 
               INNER JOIN dimdate dd 
                       ON dd.date_sid = sale.orderdate_sid 
               LEFT JOIN dimcar car 
                      ON car.car_sid = sale.car_sid 
        GROUP  BY Car.carmodeltype, 
                  Car.carmodeldetails, 
                  Car.manufacturername, 
                  dd.year) foo 
GROUP  BY year, 
          carmodeltype, 
          carmodeldetails, 
          manufacturername 
HAVING Concat(Cast(Max(most_cars_sold) AS NVARCHAR(255)), year) IN (SELECT 
       Concat(Cast(Max(most_cars_sold) AS NVARCHAR(255)), year) 
                                                                    FROM   ( 
       SELECT 
              Count(*) most_cars_sold, 
              Car.carmodeltype, 
              Car.carmodeldetails, 
              Car.manufacturername, 
              dd.year 
                                                                            FROM 
              factsales sale 
              INNER JOIN dimdate dd 
                      ON dd.date_sid = sale.orderdate_sid 
              LEFT JOIN dimcar car 
                     ON car.car_sid = sale.car_sid 
              GROUP  BY Car.carmodeltype, 
                        Car.carmodeldetails, 
                        Car.manufacturername, 
                        dd.year) foo 
       GROUP  BY year) 
ORDER  BY year

What i have done :) i've concat the cars_sold with year to make a unique reference later on i've use the same query on the having clause to compare the uniqueness with the query based only on most_cars_sold and year..

i like it :D

the result is

62000   6   2000    406     Break HDI 110pk XT  Peugeot
62001   6   2001    406     Break HDI 110pk XT  Peugeot
42002   4   2002    Astra   2.2DTi Njoy 5drs    Opel 
42002   4   2002    Vectra  GTS 2.2DTi Elegance Opel 
52003   5   2003    Laguna  Gr.Tour dCi 100pk Auth. Renault
42004   4   2004    406     Break HDI 90pk XT   Peugeot
42004   4   2004    Zafira  2.2DTi Elegance     Opel 
42004   4   2004    Passat  1.9TDI 74kW Comfortline Volkswagen
42005   4   2005    Passat  Var.TDI 96kW H5 Comfl.  Volkswagen
42005   4   2005    Polo    1.4TDI Atlantic 5drs    Volkswagen
42005   4   2005    Megane  1.9dCi Privileg.Comf.5d Renault
52006   5   2006    406     Break HDI 90pk XT   Peugeot
22007   2   2007    A   160CDI Elegance     Mercedes-Benz
22007   2   2007    Golf    1.9TDI 74kW Basis Tip.3dr   Volkswagen
22007   2   2007    Ibiza   1.9TDi 96kW Sport 3drs  Seat

Transact-SQL - Max count with an extra group by

4 Answers4