Get average sales amount for the sales persons whose birth date comes in the current month

Here is the query I have try:-

select firstname,
salesamt,
avg(salesamt)
from salesperson sp, salesfact s
where sp.salespersonkey=s.salespersonkey and month(birthdate)=month(now())
group by firstname;


Here is the table records:

2 Answers
2

if you need avg for fistname then you shoud not add saleamnt in select clause

select firstname
,avg(salesamt)
from salesperson sp
INNER JOIN salesfact s ON sp.salespersonkey=s.salespersonkey
and month(birthdate)=month(now())
group by firstname;


if you use the same column in aggregation function and in group by you can't obtain the aggregated result

And you should not use the older implicit join sintax based on where clause you should use the more recent explicit join sintax based on INNER JOIN

You can try with inner join but from your table it seems you'll not get any records
as one user only have birthday in current month and he has no records in salesfacta table

select firstname,avg(salesamt) from salesperson sp inner join
salesfact s on sp.salespersonkey=s.salespersonkey
where month(birthdate)=month(now())
group by firstname


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.