Writing a SQL Server Query to find Totals

Harreni
Harreni used Ask the Experts™
on
Hi Experts,

I want to write a SQL Server query for the attached table  to find the below:
1- Total number of service related to Consumer # AC056 without duplication.
2- Total number of datasets related to Consumer # AC056 without duplication.
3- Total number of providers related to Consumer # AC056 without duplication.
4- Total number of datasets for each service ID without duplication.

Capture.PNG


Thanks a lot.
Harreni
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Please define when a record is duplicated. Which columns should be considered? Which columns are the primary key?
Use the above table to mark the duplicated records.
Database Engineer
Commented:
As I can understand your request, I'd say :
For points 1 to 3
select ConsumerID as Consummer,count(distinct ServiceID) as NbService, count(distinct DatasetID) as NbDataset,
count(distinct ProviderId) as NbProvider
from Table
group by ConsumerID;

Open in new window


For point n°4
select ServiceID, count(distinct DatasetID) as NbDataset
from Table
group by ServiceID;

Open in new window


Don't forget if you have null(s) they don't count.
Most Valuable Expert 2011
Top Expert 2012

Commented:
select count(distinct serviceid) from yourtable where consumerid = 'AC056';

select count(distinct datasetid) from yourtable where consumerid = 'AC056';

select count(distinct providerid) from yourtable where consumerid = 'AC056';

select count(distinct datasetid) from yourtable where consumerid = 'AC056';

Author

Commented:
Thanks a lot Vitor, Docteur_Z and sdstuber.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial