Summarizing data by multiple columns

Question

My boss is asking me to code a report that has the following components:

• A pie chart of employee count by state
• A pie chart of employee count by age bracket (10 year brackets)
• A pie chart of employee length of service (5 year brackets)
• A pie chart of employee Male/Female breakdown
• A pie chart of employee count by salary band (computer generates brackets).

There may be others.

I know I can do this by writting 5 different sql statements. However it seems like this would generate 5 table scans for one report.

I could switch gears and do one table scan and analyse each record on the front end and increment counters and probably accomplish this with one-pass.

Which way would the collective wisdom at stackoverflow go on this?

Is there a way to accomplish this with the CUBE or ROLL UP clauses in T-SQL?

Answer 1

if you want 5 pie charts and need to summarize then you need 5 SQL statements since your WHERE clause is different for each

Answer 2

If your data is properly indexed, those reports may not require any table scans at all.

Really, for a problem like this you should code up the reports the simple way, and then see whether the performance meets the business requirements. If not, then look at optimisation strategies.

Answer 3

You may have some performance gains by storing intermediate results in a table variable or temp table, then running more aggregation against it. Example with only two result sets:

SELECT COUNT(*) as cnt, State, AgeBracket 
INTO #t
FROM YourTable
GROUP BY State, AgeBracket;

SELECT SUM(cnt) AS cnt, State FROM #t GROUP BY State;
SELECT SUM(cnt) AS cnt, AgeBracket FROM #t GROUP BY AgeBracket;