Derived table query to get individual field counts

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 2005 Forums

Transact-SQL (2005)

Derived table query to get individual field counts

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-02 : 11:56:16

Hello

I'm trying to return data in one query using multiple count queries.

At the moment I have individual count queries that returns my data i.e.

Select Count(distinct.accountid) as DEALS
From SALES.dbo.STATUS

Select Count(distinct.accountid) as REGISTERED
From SALES.dbo.STATUS
Where Status.Type = 'Registered'

Select Count(distinct.accountid) as CANCELLED
From SALES.dbo.STATUS
Where Status.Type = 'Cancelled'

Select SUM(Status.Amount) as TOTAL
From SALES.dbo.STATUS
Where Status.Type = 'Registered'

At the moment they are all individual queries, what I want is just one query so that the outcome of each select statement is an individual column going across.

I thought I could use a dervived table type of query but i'm unsure whether I would need to use UNION within the query.

Any help would be greatly appreciated.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 12:41:34

Select
sum(case when Status.Type = 'Registered' then 1 else 0 end) as REGISTERED,
sum(case when Status.Type = 'Cancelled' then 1 else 0 end) as Cancelled,
count(*) as Deals,
sum(Amount) as Total
From SALES.dbo.STATUS

Madhivanan

Failing to plan is Planning to fail

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-03 : 04:09:35

Thanks madhivanan

Thats exactly what I needed to do.

One final question, I want to divide the total by the amount registered.

I thought it would be quite simple to achieve doing something like:

Select
sum(sum(Amount)/(sum(case when Type = 'Registered' then 1 else 0 end)) as [AMT Paid]
From SALES.dbo.STATUS

The above doesn't work.

I have tried doing a simple query trying:
SUM(200/5) as AMT

This runs okay but gives me unexpected results (480) when I was expecting 40.

Have you any idea why this is occurring?

Am I missing some other function?

Thanks again in advance.

Dave

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-03 : 04:18:37

1.0*sum(Amount)/sum(case when Type = 'Registered' then 1 else 0 end) as [AMT Paid]

Madhivanan

Failing to plan is Planning to fail

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-03 : 07:35:15

Thanks again madhivanan thats worked a treat.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-06 : 04:47:34