Aggregation desperation - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Aggregation desperation

Author

Topic

Roel
Starting Member

3 Posts

Posted - 2013-08-06 : 04:30:53

Hi. I have recently started working with A BI tool (Microstrategy) and we are using a SQL Server 2012 database.
I have done some very basic SQL programming, but I am at a loss right now.

The situation:

I have an aggregate FilesAGG table with the following fields:
- department
- supplier
- city
- total nr of files
- nr of files with indicator 1
- nr of files with indicator 2
- nr of files with indicator 3

This table should contain the aggregated number of files and the number of files for which indicator 1/3 is true (or =1) per department/supplier/city

The fact tables on which I want to calculate are:

The Files table, wich stores the department, city and indicators :

- Filenr
- Department
- Customer
- City
- indicator 1
- indicator 2
- indicator 3

The Files Detail table:

- Filenr
- File sequence nr
- Supplier
(NOTE: the indicators at File-level apply to all suppliers in the detail table, since this may look odd)

I have tried using a SELECT statement (inside MERGE on dep/suppl/city) :
SELECT
Files.department
FilesDetail.supplier
Files.city
COUNT(distinct Files.Filenr) /* works fine */
SUM(Files.Indicator 1) /* goes wrong: duplicate counts )
FROM Files
INNER JOIN FilesDetail on filesdetail.filenr = files.filenr
GROUP BY
Files.department
FilesDetail.supplier
Files.City

The indicator should be counted once for every DISTINCT Filenr found, not for every FileDetail record found.

Any help would be greatly appreciated!!!

Regards
Roel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 04:53:16

Are 1/2/3 mutually exclusive?
There can be no row with both 1 and 2 set? Or 1 and 3? Or 2 and 3?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 05:02:50

Try this

SELECT		f.Department,
		fd.Supplier,
		f.City,
		COUNT(DISTINCT f.FileNr),
		COUNT(DISTINCT CASE WHEN f.Indicator_1 = 1 THEN f.FileNr ELSE NULL END),
		COUNT(DISTINCT CASE WHEN f.Indicator_2 = 1 THEN f.FileNr ELSE NULL END),
		COUNT(DISTINCT CASE WHEN f.Indicator_3 = 1 THEN f.FileNr ELSE NULL END)
FROM		dbo.Files AS f
INNER JOIN	dbo.FilesDetail AS fd ON fd.FileNr = f.FileNr
GROUP BY	d.Department
		fd.supplier
		f.City;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Roel
Starting Member

3 Posts

Posted - 2013-08-06 : 05:22:15

Thanks, I will try this as soon as possible !!

quote:

Originally posted by SwePeso

Try this

SELECT		f.Department,
		fd.Supplier,
		f.City,
		COUNT(DISTINCT f.FileNr),
		COUNT(DISTINCT CASE WHEN f.Indicator_1 = 1 THEN f.FileNr ELSE NULL END),
		COUNT(DISTINCT CASE WHEN f.Indicator_2 = 1 THEN f.FileNr ELSE NULL END),
		COUNT(DISTINCT CASE WHEN f.Indicator_3 = 1 THEN f.FileNr ELSE NULL END)
FROM		dbo.Files AS f
INNER JOIN	dbo.FilesDetail AS fd ON fd.FileNr = f.FileNr
GROUP BY	d.Department
		fd.supplier
		f.City;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Roel
Starting Member

3 Posts

Posted - 2013-08-06 : 07:59:06

Thanks a lot, solution works like a charm !!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 08:15:21

You're welcome.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources