How to get top 10 records - 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)

How to get top 10 records

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-24 : 14:22:55

I need the total of the gift amount that has been paid and also the highest.

My query was

SELECT DISTINCT TOP 10

c.coreid as donorid,

n.nameplural,SUM(g.giftamount) as totalamount

FROM corebio_full as c INNER JOIN name_full as n

ON c.coreid = n.nameid JOIN gifts_full AS g

ON c.coreid = g.giftid JOIN address_full as a

ON c.coreid = a.addrid JOIN attribute_full as attr

ON c.coreid = attr.attrid

WHERE n.nametype ='a' and g.gifttype ='y' or g.gifttype = 'b'

and (a.addrcrdate <= getdate())

and (attr.attrstop is null or attr.attrstop > getdate())

GROUP BY c.coreid,n.nameplural,g.giftamount

-- ORDER BY c.coreid,n.nameplural,SUM(g.giftamount) ASC

-- and (attr.attrstop is null or attr.attrstop > getdate())

Lets say there are 25 donors , I need Top 10 donors with the total gift amount they made. When I ran this query that I wrote it is not pulling the total sum. That's what I need.

Please I need urgently this answer.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 14:45:56

Take a look at the WHERE's. They are filtering out a lot of records, I think. Try to run the query with the WHERE's removed.

Peter Larsson
Helsingborg, Sweden

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-24 : 14:56:10

I need the where's it has to filter the records but, I need top 10 list.

tack,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 15:24:54

Then remove the GIFTAMOUNT from GROUP BY clause.

Varsågod.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-24 : 15:27:41

I doubt that you really want to group by g.giftamount, since you are also doing SUM(g.giftamount).

More than likely, you should remove g.giftamount from the group by.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 15:28:42

Maybe something like this will do?

SELECT		c.coreid donorid,
		n.nameplural,
		SUM(g.giftamount) totalamount,
		MAX(g.giftamount) maxamount,
FROM		corebio_full as c
INNER JOIN	name_full as n ON c.coreid = n.nameid
INNER JOIN	gifts_full AS g ON c.coreid = g.giftid
INNER JOIN	JOIN address_full as a ON c.coreid = a.addrid
INNER JOIN	attribute_full as attr ON c.coreid = attr.attrid
WHERE		n.nametype = 'a'
		and g.gifttype IN ('y', 'b')
		and a.addrcrdate <= getdate()
		and (attr.attrstop is null or attr.attrstop > getdate()) 
GROUP BY	c.coreid,
		n.nameplural

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources