Using the Least Function - 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

Using the Least Function

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-29 : 14:39:13

I have the following query

Select least(sum(volumn) from sales where product_id = 15

But I get this error, Incorrect parameter count in the call to native function 'least'

The database shows the volumn by day by product_code

sample date;

Date Product Code Volumn
04/01/2011 11 15
04/01/2011 11 18
04/01/2011 12 15
04/01/2011 12 18

I want to take the lower volumn of each day by product code, so for 04/01, the total volumn should be 30 since 15 is the lower volumn of product code 11 and 12.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 14:48:27

[code]select
[date],
sum(minVolForProduct )
from
(
select [date],min(Volumn) as minVolForProduct from YourTable group by [date],[Product Code]
)s
group by [date][/code]

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-29 : 14:50:16

quote:

Originally posted by sunitabeck

select
   [date],
   sum(minVolForProduct )
from
(
  select [date],min(Volumn) as minVolForProduct from YourTable group by [date],[Product Code]
)s

I don't understand this code

What is minVolForProduct?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 14:52:30

quote:
Originally posted by fengfeng

I don't understand this code

What is minVolForProduct?

It is taking the lowest quantity for each product each day. That is what I call minVolForProduct. In your example, it is 15 for each of product code 11 and 12. Then, adding those up.

Or, did I misunderstand what your requirement is?

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-29 : 14:59:05

quote:
Originally posted by sunitabeck

quote:
Originally posted by fengfeng

I don't understand this code

What is minVolForProduct?

It is taking the lowest quantity for each product each day. That is what I call minVolForProduct. In your example, it is 15 for each of product code 11 and 12. Then, adding those up.

Or, did I misunderstand what your requirement is?

But I dont have a field called minVolForProducts

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 15:04:24

Oh! That is just an alias that I created. see the items I have highlighted in red, green and blue below.

select
   [date],
   sum(minVolForProduct )
from
(
  select [date],min(Volumn) as minVolForProduct from YourTable group by [date],[Product Code]
)s
group by [date]

The way to read it is:

Step 1 (red): I am calculating the min value of volumn and then calling it minVolForProduct.

Step 2 (green): That query is wrapped up as a sub-query. The output of the sub-query is a derived table that I call s.

Step 3 (blue): Select from that derived table. The derived table knows about the minVolForProduct column because I defined that in the subquery.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 15:05:06

Try running the code and if it complains, copy and post the error message.

Are you using SQL Server or some other DBMS?

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-29 : 15:10:20

quote:
Originally posted by sunitabeck

Oh! That is just an alias that I created. see the items I have highlighted in red, green and blue below.
select
[date],
sum(minVolForProduct )
from
(
select [date],min(Volumn) as minVolForProduct from YourTable group by [date],[Product Code]
)s
group by [date]

The way to read it is:

Step 1 (red): I am calculating the min value of volumn and then calling it minVolForProduct.

Step 2 (green): That query is wrapped up as a sub-query. The output of the sub-query is a derived table that I call s.

Step 3 (blue): Select from that derived table. The derived table knows about the minVolForProduct column because I defined that in the subquery.

What if i just want to show the sum and no date?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 15:24:22

If you just want to hide the date, but still group by the dates remove the red code. If you don't want to consider dates at all, remove the red and green code - both green or only the outer green depending yon what you want to get. Experiment with it and see if either gives you the result you are looking for.

select
   [date],
   sum(minVolForProduct )
from
(
  select date],min(Volumn) as minVolForProduct from YourTable group by [date],[Product Code]
)s
group by [date]

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-04-29 : 15:48:26

quote:
Originally posted by sunitabeck

If you just want to hide the date, but still group by the dates remove the red code. If you don't want to consider dates at all, remove the red and green code - both green or only the outer green depending yon what you want to get. Experiment with it and see if either gives you the result you are looking for.
select
~~[date],~~
sum(minVolForProduct )
from
(
select ~~date],~~min(Volumn) as minVolForProduct from YourTable group by ~~[date],~~[Product Code]
)s
~~group by [date]~~

Is there a way to incorporate that into this query:

IFNULL(SUM( CASE WHEN gaa.date>= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) THEN
gaa.volumn
END ),0) AS 'volumn_Yesterday'

so I want to take the smaller amount from yesterday

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 16:46:37

If you meant that you want to do the same calculation, but only use the data from yesterday:


select
   sum(minVolForProduct )
from
(
  select min(Volumn) as minVolForProduct from YourTable 
  where date = dateadd(dd,datediff(dd,1,getdate()),0) 
  group by [Product Code]
)s

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources