Adding the amount field - 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 2005 Forums

Transact-SQL (2005)

Adding the amount field

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-06-14 : 06:17:08

Hi I have a query (see below) that shows the company name, contact name, city, plus their status (key1) andI am using "group by" to show the most recent sale.
I have one more field I would like to include in the SELECT it shows the sale amount and is called conthist.amount
I can't figure out how to add it because if I add it to the select i must add it to the group by and that then creates multiple rows per customer.

SELECT CONTACT1.COMPANY, CONTACT1.CONTACT, CONTACT1.CITY, CONTACT1.KEY1, MAX(CONTHIST.ONDATE) AS LastSale
FROM CONTACT1 INNER JOIN
CONTHIST ON CONTACT1.ACCOUNTNO = CONTHIST.ACCOUNTNO
WHERE (CONTHIST.RECTYPE = 'S')
GROUP BY CONTACT1.COMPANY, CONTACT1.CONTACT, CONTACT1.CITY, CONTACT1.KEY1
ORDER BY LastSale DESC, CONTACT1.COMPANY

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 06:23:53

Use sum(conthist.amount)

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-06-14 : 06:24:49

Add sum(conthist.amount)over(partition by contact1.company) in the select list.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-06-14 : 06:29:33

Thanks
I'm not looking for the total sale amount, just the sale amount for the most recent sale.
Do I still need SUM

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:16:47

Try this

SELECT A.COMPANY
, A.CONTACT
, A.CITY
, A.KEY1
, B.ONDATE AS LastSale
, B.AMOUNT
FROM CONTACT1 A INNER JOIN
(
SELECT ROW_NUMBER() OVER(PARTITION BY ACCOUNTNO ORDER BY ONDATE DESC) AS SEQ
, * FROM CONTHIST 
WHERE RECTYPE = 'S'
) B 
ON A.ACCOUNTNO = B.ACCOUNTNO AND B.SEQ = 1
ORDER BY LastSale DESC, A.COMPANY

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-06-15 : 00:05:09

That's great.
i wasn't aware of the Partition by function

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-15 : 08:55:34

Np. You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources