Sql-server – Vendors with Most Invoices in State

aggregatesql serversql-server-2012subquery

I have the tables Vendors(VendorName, VendorID,…) and Invoices(InvoiceID,…) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:

SELECT Max(NumInvoicesState)
FROM 
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState 
   FROM VENDORS V 
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) AS Alias

My idea is to first do a query to get a table with a list of vendors by state and the number of invoices for each vendor.

I thought of joining the inner-table to Vendors and then group by VendorState, but that does not seem to work.

I guess I could SELECT VendorName, VendorState then join it with Vendors on, say VendorState and then Group by Vendors. But then I get all the Vendors, not just the ones with the max.

Best Answer

I think the inner query is right.

The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state.

Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP BY, but that will give the wrong result. It is necessary to do this in two steps. In the outer query just deal with states:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Then join this back to the inner query on the MAX() value:

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

In other words, here are the most numerous invoices in each state. Which vendors have these many invoices?

If two vendors have the same number of invoices in a state there will be two rows output.

Since the inner query is repeated verbatim it would be better presented as a non-recursive CTE.

SQL Server 2012 has a number of windowing functions built in. The most useful one here is RANK (). It does just what you think it should do.

Select 
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

This eliminates all of the second-order dataset alignment on derived aggregate values required above. It is much easier to read and understand, and hence to maintain.

Related Solutions

Sql-server – Confusing Query: Mixing Aggregates and Non-Aggregates

The solution is what you already found, in DBMS like SQL Server 2005+ that have implemented window functions, we can use them to get aggregates over each partition while keeping the original table, so not collapsing the table as a group by does. I'm not writing the query here, please edit your answer.

In older versions (or other DBMS) that don't have the OVER () clause syntax available, we could use GROUP BY in a derived table (or a CTE) and then a "self" join back to the original table, like this:

SELECT 
    i.VendorID, i.InvoiceDate, i.InvoiceTotal, 
    grp.VendorTotal, 
    grp.VendorCount, 
    grp.VendorAvg 
FROM
      dbo.Invoices AS i             -- our original table
  JOIN
      ( SELECT 
            VendorID,
            Sum(InvoiceTotal) AS VendorTotal, 
            Count(InvoiceId) AS VendorCount, 
            Avg(InvoiceTotal) AS VendorAvg 
        FROM 
            dbo.Invoices  
        GROUP BY
            VendorID 
      ) AS grp                      -- the aggregated table 
  ON 
      grp.VendorID = i.VendorID ;

Sql-server – Query Motivation for Cross Joins

CROSS JOINs are generally used to explode a data set in order to produce a large number of rows. For example, I use CROSS JOINs to create my Numbers tables.

There are other practical purposes, though. Let's say you have a table of permissions:

CREATE TABLE dbo.BasePermissions
(
  PermissionID INT PRIMARY KEY,
  Description VARCHAR(32)
);
INSERT dbo.BasePermissions VALUES(1, 'Do stuff'),(2, 'Do other stuff');

Now, you probably have a table mapping UserID -> PermissionID, but this isn't really necessary for admin users. So in that case you might have the following query (let's say the application does need the granular permissions for each user, or you have exceptions for specific users you might apply later, etc.):

SELECT u.UserID, p.PermissionID, p.Description
FROM dbo.Users AS u
CROSS JOIN dbo.BasePermissions AS p
WHERE u.IsAdmin = 1;

This will yield a set of rows (number of admin users * number of permissions), with every permission listed for every admin user. It avoids you having to store every single set, and also means you don't have to update all admin users when you add or remove a permission.

(This is not a super practical example, because the app could be written in such a way that recognizes an admin user, but let's say the app can't be changed.)

As an aside, resist the temptation to ever use a RIGHT JOIN. Stick to LEFT, it's a much simpler way to think about relationships conceptually IMHO. Every time I see a RIGHT JOIN I re-write it to LEFT before I do anything else.

Best Answer

Related Solutions

Sql-server – Confusing Query: Mixing Aggregates and Non-Aggregates

Sql-server – Query Motivation for Cross Joins

Related Question