Pb to retreive max value with aggregate subquery

I would like to know which products are my best sells by sellers, but i
would like to retreive this info by product id, seller id and the total
amount of sells for this product.

My Sells table is :
Seller_idProduct_idTotaldate_s
1 2 1020/05/04
2 4 1512/05/04
3 5 2206/06/04
1 5 1807/06/04
4 8 1213/05/04
7 2 1119/05/04
3 4 1421/05/04
2 4 1418/05/04
1 5 1817/06/04
2 5 5008/05/04

etc...

I know how to retreive the total sells by product id and seller id

SELECT Seller_id, Product_id, SUM(Total) AS total
FROM Sells
WHERE date_s > '01/05/04'
GROUP BY Seller_id,Product_id order by Seller_id

Seller_idProduct_idTotal
1 5 36
1 2 10
2 5 50
2 4 29
3 5 22
3 4 14

I would like retreive only the max of total, and the Seller id and
product id, like this :

Seller_idProduct_idTotal
1 5 36
2 5 50
3 5 22

How can i do without using a temp table ?

Thanks for your help.Try this:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id) AS T) ;

--
David Portas
SQL Server MVP
--|||David Portas wrote:

> Try this:
> SELECT seller_id, product_id, SUM(total) AS total
> FROM Sells AS S
> WHERE date_s > '20040501'
> GROUP BY seller_id, product_id
> HAVING SUM(total) =
> (SELECT MAX(total)
> FROM
> (SELECT SUM(total) AS total
> FROM Sells
> WHERE date_s > '20040501'
> AND seller_id = S.seller_id
> AND product_id = S.product_id) AS T) ;

Thank but i think i need to add a GROUP by in the last select:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id GROUP BY seller_id, product_id )
AS T) ;

Thanks a lot|||Almost. I think you wanted the max for each Seller_ID so you just need
Seller_ID in that WHERE clause and Product_ID in the GROUP BY - which is
what I should have posted to start with.

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
GROUP BY product_id ) AS T) ;

--
David Portas
SQL Server MVP
--

Passing table Column name as parameter to a stored procedure

I want to run a Stored Procedure which takes Column name( table column names ) as input, use some aggregate function and return the desired output.

I have tried passing a single column name as input parameter and also the entire SQL statement as input parameter. but i am not able to capture and return the output value

I have also tried using Table data type but fail to capture the value. I dont want to use Temporary table.

The syntax i tried is some thing like this

Declare @.stmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.rtval = @.stmt

And also

Declare @.AcctCode Char(8)
Declare @.Ssql NVarchar(100)
Declare @.rtVal numeric (18,5)
Set @.AcctCode = 'An_Sales'

Set @.Ssql = 'Select ' +@.rtval + '=AVG(an_sales) into From T_Comp_Profile'
Exec SP_ExecuteSql @.Ssql
print @.rtval

Pls help me in this regard

Ramanbir Singhtry something like...

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt|||Dear Rockslide

U have wriiten me with the following code

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt

The stt "Exec sp_executesql @.mystmt" this returns a data set, so it is not going to be stored in a variable like u specified i.e
Exec sp_executesql @.stmt= @.mystmt

because when we print the value of @.stmt using "Print @.stmt" it returns nothing also we cant use a table data type here in place of @.stmt|||hi rjaj

I think I am a little confused.

sp_executesql takes (basically) 2 different parameters, see the syntax below.

sp_executesql [@.stmt =] stmt
[
{, [@.params =] N'@.parameter_name data_type [,...n]' }
{, [@.param1 =] 'value1' [,...n] }
]

When we say

exec sp_executesql @.stmt=@.mystmt

we are effectively saying

exec sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

if we do nothing else with the returned results they will be outputed.

if you want the results to return to a parameter then you would need to do something like

select @.results = sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

at a guess (the line above hasn't been tested, in theory I think it should work).|||Sounds familiar

http://www.dbforums.com/t970045.html|||create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)|||Originally posted by ms_sql_dba
create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)

Using temporary tables work
but i dont want to use a temporary table
Is there any other way for that