mercredi 22 juin 2016

Transact-SQL - Max count with an extra group by

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:

6   2000
6   2001
4   2002
5   2003
4   2004
4   2005
5   2006
2   2007

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

Can somebody help?

Aucun commentaire:

Enregistrer un commentaire