SQL divide query

WeTi
WeTi used Ask the Experts™
on
Dear expert i need help on this sql query:

select sum (money)
from dayground
where Daycode = 1 and Typecod = 4 and date = '20790606'
and money >= 25000

divide by

select count (memberno)
from dayground
where Daycode = 1 and Typcod = 4 and date = '20790606'
and money >= 25000

Anyway to do it?
Thx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
what about this query?

select
(
select sum (money)
from dayground
where Daycode = 1 and Typecod = 4 and date = '20790606'
and money >= 25000
)

/

(
select count (memberno)
from dayground
where Daycode = 1 and Typcod = 4 and date = '20790606'
and money >= 25000
)

Open in new window

Author

Commented:
Very fast and correct answer. thanks man
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What database?

Am I missing the reason you cannot do:
select sum (money)/count (memberno)
from dayground
where Daycode = 1 and Typecod = 4 and date = '20790606'
and money >= 25000

Open in new window


You will need to worry when the count = 0.  What do you want to happen then?  It would likely be a CASE statement.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Author

Commented:
One more thing, how do you round the result to 0 decimal?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>how do you round the result to 0 decimal?

Depends on the database product.  Most databases have a round function.  Check the docs?

Still curious why you couldn't do the sum and count in the same query...
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you can surely cast:
select cast (
(
select sum (money)
from dayground
where Daycode = 1 and Typecod = 4 and date = '20790606'
and money >= 25000
)
/
(
select count (memberno)
from dayground
where Daycode = 1 and Typcod = 4 and date = '20790606'
and money >= 25000
)
as int)

Open in new window

Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
a good point from NetMinder, your query would be faster by doing a single query:

select cast(sum(money) / count (memberno) as int)
from dayground
where Daycode = 1 and Typcod = 4 and date = '20790606'
and money >= 25000

Open in new window

Author

Commented:
Because im a beginner and didn't know how to use cast fully

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