Efficient use of SQL GROUP BY, SUM, COUNT

Question

I have a table of products sales that may look as follows:

product   | amount   | ptype    | pdate      
p1        | 1.00     | sale     | 01/01
p1        | 2.00     | base     | 01/02
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

and I would like to build a table that shows one product per row, the sum of the amounts, if the product is unique show the type else show the type as 'VAR', if the product is unique show the date else show the date as NULL. So that the result look as follows:

product   | total    | ptype    | pdate      
p1        | 3.00     | VAR      | (NULL)
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

I am accomplishing the result I need by doing the following:

SELECT DISTINCT product
,(SELECT SUM(amount) FROM T as b GROUP BY b.product HAVING a.product = b.product ) as total
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.ptype  ELSE 'VAR' END from T as b GROUP BY b.product HAVING a.product = b.product) as ptype
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.pdate  ELSE NULL END from T as b GROUP BY b.product HAVING a.product = b.product) as pdate
FROM T as a

But I would like to know if there is a more efficient way that accomplishes the same result.

Solution 1

There is no need to use any form of subquery or inline view. Depending on the sophistication of the database engine, those constructs could negatively impact performance.

Here is what you requested, and it should reliably give the result with a single scan of the table on even the most primitive SQL engine.

select product,
       sum(amount) as amount,
       case when count(*)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(*)=1 then min(pdate) else null end as pdate
  from T
 group by product

The following is not exactly what you requested, but I think it may be closer to what you are actually looking for. It only reports ptype as VAR or pdate as NULL if there are multiple distinct values making up the aggregate.

I've added a pcount column so that you can still identify singlet aggregates, even if both ptype and pdate are not nulll.

select product,
       sum(amount) as amount,
       count(*) as pcount,
       case when count(distinct ptype)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(distinct pdate)=1 then min(pdate) else null end as pdate
  from T
 group by product

Solution 2

If you're running this against any major database (such as SQL Server), the query optimizer will probably take care of most of the optimization on your behalf. That said, you could do this pretty easily with an inner query that has a single group by. Here is an example that works and you can play with:

http://sqlfiddle.com/#!3/f2e05/19/1

Comments

[Mark Stafford - MSFT]

This SQL will not run on SQL Server; not sure about other database engines.

[Mark Stafford - MSFT]

My query does pretty much the same as the first query offered by @dbenham; I made the same observation that it might not be what you actually want. I'll upvote that answer. :)

[Daniel Vaca]

In Sybase, this gives me an error saying that 'ptype' and 'pdate' must also appear in GROUP BY.

[Palladium]

Wow, I got caught with my pants down on that one. Completely forgot that to do group functions you need to actually group the table.