sum a count - 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

SQL Server 2000 Forums

SQL Server Development (2000)

sum a count

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-04-01 : 10:53:51

I have the following statement

select count(distinct s.job_id), sum(case when type = 'I' then valuegros
when type = 'C' then (-1 * valuegros)
else 0 end) as 'valuegros'
from tblSalesInvoice s
join tblEstimate e on e.job_id=s.job_id
where e.date_fully_invoiced <= '20050228' and e.date_fully_invoiced >= '20050201'group by s.job_id

which gives me

1 | 46632.74
1 | 7832.55

what I want to achieve is to have

2 | 54465.29 | 27232.65

with the first 2 columns being the sums of the count and sum and the third being the average i.e. column 2 / column 1

Is this possible?

jparker
Posting Yak Master

118 Posts

Posted - 2005-04-01 : 10:57:50

Worked it out for myself : -

select count(distinct s.job_id), sum(case when type = 'I' then valuegros
when type = 'C' then (-1 * valuegros)
else 0 end) as 'valuegros', sum(case when type = 'I' then valuegros
when type = 'C' then (-1 * valuegros)
else 0 end) / count(distinct s.job_id)
from tblSalesInvoice s
join tblEstimate e on e.job_id=s.job_id
where e.date_fully_invoiced <= '20050228' and e.date_fully_invoiced >= '20050201

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources