Member Avatar for Dumb Fish

I not sure how to do this.Could anyone can help me...?

question:
Display all buyer (code, name) who has average purchase each year for more than 100000 for past 10 years


here is my query:

SELECT b.CODE, b.NAME from 
(
SELECT BUYER, AVG(TOTAL) AS total 
FROM SMF
WHERE TOTAL > 100000 
and PERIOD1 > DATEADD(YEAR, -10, GETDATE())
group by BUYER
) a
inner join ACCODE b on b.CODE = a.BUYER

thank you for helping....

Edited by Ezzaral because: Added code tags. Please use them to format any code that you post.
  • 2 Contributors
  • 1 Reply
  • 106 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi 
SELECT b.CODE, b.NAME,a.BUYER, AVG(a.TOTAL) AS total 
FROM SMF a
WHERE  a.PERIOD1 > DATEADD(YEAR, -10, GETDATE())
group by b.CODE, b.NAME,a.BUYER
having AVG(a.TOTAL)>100000 
inner join ACCODE b on b.CODE = a.BUYER
Edited by urtrivedi because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.