Reports Multiple counts with multiple group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reports Multiple counts with multiple group by

Author

Topic

pixelwiz
Starting Member

25 Posts

Posted - 2012-04-02 : 13:10:10

Ok, I don't have an easy way to provide some sample data for this, but hopefully this is a simple conceptual question for someone who's good with doing summary reports in SQL. Let me try to explain in a simplified version.

Let's say there are 3 tables: Providers, Locations, and Customers.

The report I need is for a 4 day event. There are several providers, each that have a kiosk setup at one or multiple location. So provider1 might have only 1 location, provider2 might have 3 location, and privider3 also has 3 locations.

I need to get the following:
- Cumulative totals
- Cumulative totals per provider
- How many customers bought something per provider per day of the event
- How many customers bought something per location (total per location)
- How many customers bought something per location per day (day totals)

And then it gets even more interesting cause I need a count of people who provided their email address out of those who purchased something for each category.

Can someone please give me a general example for how to do this? The only way I can think off is running the big query in SQL to get all the data, and then looping over that query in my code to get all the subtotals. There must be a way to do it in SQL though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 16:16:56

you just need to apply group on required fields based on which you want to do aggregation ie provider,location etc

then apply aggregates using SUM(),COUNT() etc over fields to get required figures. for additional filters put them either in a WHERE Clause or with SUM(),COUNT() using case when

if calculations are to be done independetly then create a tenporary table with require field and use seperate update for updating each values on the table for each provider,location etc combination
no need of loop for that. use set based logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-02 : 16:47:39

Your totals are largely aggregations of either raw data or previous aggregations. Have you considered using a set of temp tables (or table variables) that build on one another and then using those tables in a series of queries?[CODE]declare @MyTable table (
Customer varchar(40),
Provider varchar(40),
Location varchar(40),
PurchaseDate date
)

select Customer, Provider, Location, PurchaseDate
into #RawData
from @MyTable
where <Your selection criteria>

select Provider, PurchaseDate, count(distinct Customer) CntCustomer
into #CustPerProvDate
from #RawData
group by Provider, PurchaseDate

select Location, PurchaseDate, count(distinct Customer) CntCustomer
into #CustPerLocDate
from #RawData
group by Location, PurchaseDate

select Location, sum(CntCustomer) CntCustomer -- (sic) The SUM of the COUNTs is the total COUNT
into #CustPerLocation
from #CustPerLocDate
group by Location

select Provider, sum(CntCustomer) CntCustomer -- (sic) The SUM of the COUNTs is the total COUNT
into #CustPerProv
from #CustPerProvDate
group by Provider

select sum(CntCustomer) CntCustomer -- (sic) The SUM of the COUNTs is the total COUNT
from #CustPerProv[/CODE]You can probably streamline the code and/or add indexes to the various tables, as needed, but the basic approach should work.

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources