Most optimized way to get column totals in SQL Server 2005+

Question

I am creating some reports for an application to be used by various states. The database has the potential to be very large. I would like to know which way is the best way to get column totals.

Currently I have SQL similar to the following:

SELECT count(case when prg.prefix_id = 1 then iss.id end) +
       count(case when prg.prefix_id = 2 then iss.id end) as total,
       count(case when prg.prefix_id = 1 then iss.id end) as c1,
       count(case when prg.prefix_id = 2 then iss.id end) as c2
FROM dbo.TableName
WHERE ...

As you can see, the columns are in there twice. In one instance, im adding them and showing the total, in the other im just showing the individual values which is required for the report.

This is a very small sample of the SQL, there are 20+ columns and w/i those columns 4 or more of them are being summed at times.

I was thinking of declaring some @Parameters and setting each of the columns equal to a @Parameter, then I could just add up which ever @Parameters I needed to show the column totals, IE: SET @Total = @c1 + @c2

But, does the SQL Server engine even care the columns are in there multiple times like that? Is there a better way of doing this?

Answer 1

Use straight SQL if you can before resorting to T-SQL procedure logic. Rule of thumb if you can do it in SQL do it in SQL. If you want to emulate static values with straight SQL try a inline view like this:

SELECT iv1.c1 + iv1.c2 as total,
       iv1.c1,
       iv1.c2
    FROM
    (
    SELECT count(case when prg.prefix_id = 1 then iss.id end) as c1,
           count(case when prg.prefix_id = 2 then iss.id end) as c2
    FROM dbo.TableName
    WHERE ...
    ) AS iv1

This way you logically are getting the counts once and can compute values based on those counts. However I think SQL Server is smart enough to not have to scan for the count n number of times so I don't know that your plan would differ from the SQL I sent and the SQL you have.

Answer 2

Any reason this isn't done as

select prg.prefix_id, count(1) from tablename where... group by prg.prefix_id     

It would leave you with a result set of the prefix_id and the count of rows for each prefix_ID...might be preferential over a series of count(case) statements, and I think it should be quicker, but I can't confirm for sure.

I would use a subquery before resorting to @vars myself. Something like this:

   select c1,c2,c1+c1 as total from 
   (SELECT 
   count(case when prg.prefix_id = 1 then iss.id end) as c1, 
   count(case when prg.prefix_id = 2 then iss.id end) as c2 
   FROM dbo.TableName 
   WHERE ... ) a