Help Needed / Appreciated...

[MajikTwo]

Hi
I have this query :

Select
Inv.ItemId As Item,
Sum(Inv.RowTotal) As TotSales,
Sum(Qty) as ItemQty,
(Select Sum(Qty) From Invoice) As TotQty
from Invoice Inv
Group by Inv.ItemId

It returns :

Item...... TotSales..... ItemQty...... TotQty
000001... 18510.00..... 39.00......... 90.00
000002... 28540.00..... 30.00 ........ 90.00
000003... 30790.00..... 21.00 ........ 90.00

I need to select the items when Sum(ItemQty) > = 60
In this case Item 1 and 2

Help

 

[gmmastros]

Select 
Inv.ItemId As Item, 
Sum(Inv.RowTotal) As TotSales, 
Sum(Qty) as ItemQty,
(Select Sum(Qty) From Invoice) As TotQty
from Invoice Inv 
Group by Inv.ItemId 
[!]Having Sum(Qty) >= 60[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MajikTwo]

Hi gmmastros

Thanks for your reply, but it's NO GOOD.

Having Sum(Qy) > = 60 will return nothing, it search in the Sum of each item for one >=60

It don't make a sum of the sum and return item 1 and 2.

Sorry no cigar yet.

 

[simian336]

sum of what sum?

Simi

 

[r937]

39 is not greater than 60, and neither is 30

it seems you want some kind of running total comparison? based on ascending item numbers (which is weird)

this would be a ~lot~ easier to do in your application language (e.g. php)


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MajikTwo]

Hi again
What I need is to choose the best selling items (By QTY) in my list, they should be in the Top 60% of my total sales Quantity, In this case it returns Item 1 and 2, 39 et 30.
In case I want the best Top 60% of my total Sales (Value), the answer will be Item 3 and 2, 30790.00 and 28540.00

 

[r937]

you're sure you don't want item 1 and 2, like you said in your first post?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MajikTwo]

When I'm searching for the Top Quantity Seller, It should return Item 1 and 2, by this order.

When I'm searching for the top Value Seller, It should return item 3 and 2, by this order also.

The logic is to have the top of the cream from higher to lower, in a pourcentage of top 60%.

 

[markros]

Take a look at NTILE() function in T-SQL. It should help to you find top N percent

PluralSight Learning Library

 

[markros]

Take a look at NTILE() function in T-SQL. It should help to you find top N percent

PluralSight Learning Library

 

[MajikTwo]

Sorry I'm not seeing it with NTile, from what I understood it will divide my rows into sections/Buckets.
But my problem stay the same, I need to know when to stop when my Pourcentage is 60 or more, and here is the ratios

Item...... TotSales..... ItemQty...... TotQty.... RatioQty.... RatioValue
000001.... 18510.00..... 39.00........ 90.00..... 0.4333..... 0.2377
000002.... 28540.00..... 30.00 ....... 90.00..... 0.3333..... 0.3666
000003.... 30790.00..... 21.00 ....... 90.00..... 0.2333..... 0.3955

 

[TheBugSlayer]

Even though you seem to be adding a different element in each statement you make, it seems to me two versions of the same query will give you what you want; you need to order one by ItemQty and the other one by TotSales, both in DESCENDING order and take the TOP 2 in each from each as in

with cte_sum
(
Select 
Inv.ItemId As Item, 
Sum(Inv.TotSales) As TotSales, 
Sum(ItemQty) as ItemQty,
(Select Sum(ItemQty) From @Invoice) As TotQty
from @Invoice Inv 
Group by Inv.ItemId
)
select top 2 * from cte_sum
order by totsales desc

with cte_sum
(
Select 
Inv.ItemId As Item, 
Sum(Inv.TotSales) As TotSales, 
Sum(ItemQty) as ItemQty,
(Select Sum(ItemQty) From @Invoice) As TotQty
from @Invoice Inv 
Group by Inv.ItemId
)
select top 2 * from cte_sum
order by TotQty desc

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[MajikTwo]

Hi TheBugSlayer
Thanks for your input, but usually it takes more then 2 items to fill up 60% ratio, actually it's unkwohn how much, so the Top 2 for sure no.
Anyway thank you all, I rearanged my Report and pass it thru VB and fill up my report from, instead of using only SQL, Thanks r937 (TechnicalUser) I should had followed your advice from the begining.
But you know we should try...