SQL select MAX(field) problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL select MAX(field) problem

Author

Topic

jafari1983
Starting Member

9 Posts

Posted - 2010-10-22 : 01:09:08

Hello, I have a table. I wanna show the data in DBGrid(delphi), i am getting problems.

|__description__|___quantity___|__price__|_______date_____|
|_____apple_____|_______2______|____1____|___01.01.2010___|
|______banana___|_______2______|____2____|___02.01.2010___|
|_____apple_____|_______2______|____4____|___03.01.2010___|
|______banana___|_______2______|____3____|___04.01.2010___|

I want list them with the last date and price, sum quantity.

I write this
'SELECT description, SUM(quantity) AS qty, MAX(date) AS mdate
FROM table1
GROUP BY description'

i get this result

|__description__|___quantity___|__price__|_______date_____|
|_____apple_____|_______4______|_________|_____03.01.2010_|
|_____banana____|_______4______|_________|_____04.01.2010_|

but what command must I write to get this result ?
(the price with the last date, quantity summed and distinct description)

|_description__|___quantity___|__price__|_______date_____|
|____apple_____|_____4________|__4______|___03.01.2010___|
|____banana____|_____4________|__3______|___04.01.2010___|

Thanks in advance,
Aqshin

La Fata illa Ali, la seyfa illa Zulfuqar

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-22 : 02:07:33

Hello,

You can try this:

SELECT
t.description,
SUM(t.quantity) AS qty,
(select price from table1 where description=t.description and date=max(t.date)) as mdate_price,
MAX(t.date) AS mdate
FROM
table1 t
GROUP BY
t.description

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

jafari1983
Starting Member

9 Posts

Posted - 2010-10-22 : 02:28:15

I did as You told.

it gives error
cannot have aggregate function in WHERE clause (description=t.description And date=max(t.date))

thanks a lot for reply

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 02:41:15

Are you sure you tried it???I am not getting any error



declare @tbl as table(description varchar(30), quantity int,price int,date datetime)
insert into @tbl
select 'apple',2,'1','01.01.2010' union 
select 'banana',2,'2','02.01.2010' union 
select 'apple',2,'4','03.01.2010' union 
select 'banana',2,'3','04.01.2010'
select * from @tbl

SELECT
t.description,
SUM(t.quantity) AS qty,
(select price from @tbl where description=t.description and date=max(t.date)) as mdate_price,
MAX(t.date) AS mdate
FROM
@tbl t
GROUP BY
t.description

PBUH

razeena
Yak Posting Veteran

54 Posts

Posted - 2010-10-22 : 04:59:34

Try this way too..
declare @tbl table(description varchar(30), quantity int,price int,date datetime)
insert into @tbl
select 'apple',2,'1','01.01.2010' union
select 'banana',2,'2','02.01.2010' union
select 'apple',2,'4','03.01.2010' union
select 'banana',2,'3','04.01.2010'
select * from @tbl

select description,sum(quantity) as Qnty,max(price), max(date)
from @tbl
group by description

jafari1983
Starting Member

9 Posts

Posted - 2010-10-22 : 05:51:05

'select description,sum(quantity) as Qnty, max(price), max(date)
from table1
group by description'

This works well, but it gives me the highest price, but i need the price with the last date. I am making software for a shop, at the end of month I want to check the store, want to know the last prices, existing quantity and names of things.

But first two don't work, I think, I don't do something correctly. Maybe, it doesn't work because of MS ACCESS database ? Because, I try them on ms access... But today I will check them on MS SQL too.

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 06:15:25

quote:
Originally posted by jafari1983

I did as You told.

it gives error
cannot have aggregate function in WHERE clause (description=t.description And date=max(t.date))

thanks a lot for reply

Are you sure you are using "Microsoft SQL SERVER"?

PBUH

jafari1983
Starting Member

9 Posts

Posted - 2010-10-23 : 03:34:10

I am sorry guys, Devart, it works on SQL server. Thanks a lot all of You.

La Fata illa Ali, la seyfa illa Zulfuqar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources