SQL GROUP BY techniques

[guna20]

Hello,

I need a query to solve the below problem. The table rows are given below

[B]Stock Details		
-------------------------------------------------------
[/B]ID	Product	  Color	   Rate	 Stock
1	AD.GOLD	  White	   5250	 100
2	SM	  White	   5318	 100
3	LOGU - II White	   5357	 100
4	SS(03.5)  White	   5239	 100
5	PMV	  L.White  4810	 100
6	A.DELUX	  L.White  5082	 100
7	P.MANI	  L.Yellow 5157	 100
8	SVK	  L.Yellow 5282	 100
9	SSS	  L.Yellow 5182	 100
10	P.MANI	  L.Yellow 5157	 100
11	MNK	  L.Yellow 5207	 100
12	SVK	  L.Yellow 5207	 100
13	SVK - II  L.Yellow 5232	 100
14	KPN	  L.Yellow 5075	 100
15	SS	  L.Yellow 5044	 100
--------------------------------------------------------
[B]Customer Enq :[/B]
---------------------
Color	
white	30
L.white	40
L.yellow	12
	82
---------------------------------------------------
I want the output as

---------------------------------------------------
[B]Result  -I					[/B]
ID	Product	Color	Qty	Rate	Total Rate
1	AD.GOLD	White	25	5250	131250
3	LOGU - IIWhite	5	5357	26785
6	A.DELUX	L.white	20	5082	101640
5	PMV	L.white	20	4810	96200
12	SVK	L.Yellow7	5207	36449
7	P.MANI	L.Yellow5	5157	25785
                       ----            -------
Total:			82		418109
-------------------------------------------------------
Result  -II					
4	SS(03.5)White	25	5239	130975
3	LOGU - IIWhite	5	5357	26785
6	A.DELUX	L.white	20	5082	101640
5	PMV	L.white	20	4810	96200
13	SVK - IIL.Yellow7	5232	36624
8	SVK	L.Yellow5	5282	26410
                     -------          ---------
Total:			82		418634
-----------------------------------------------------
Result  -III					
1	AD.GOLD	White	30	5250	157500
6	A.DELUX	L.white	20	5082	101640
6	SVK - IIL.Yellow7	5232	36624
15	SS	L.Yellow5	5044	25220
5	PMV	L.white	20	4810	96200
                     ------           -----------					
Total :			82		417184
-------------------------------------------------

Thanks in advance.

Regards,
Gunasekaran.K

[Luuk]

Question:
Your 'Stock Details' show 9 options for 'L.Yellow', does this mean that any combination of these nine options, with a total of 12 is valid?

That's a lot of combinations (about 800, is i'm not mistaken) ;-)

[guna20]

VB6 Source code Help

Dear All,

I Need a Vb6 Source code to solve the below problem. Please Find attachment of XL File,

Sheet 1 : Product Details with Color, Condition Rank & Rate.

Sheet 2 : Example Customer Enquiry,


I Need Total Qty : 82


Color Qty
white 30
L.white 40
L.yellow12
Total 82

Check with the Given Condition,

Condition Qty
1/4 50
1/4 - 20
LS 1/4 7
LS 1/4 - 5
Total 82

Shows the Results, all set of Availabilities, in XL sheet we manually given 3 sets.

how many sets its match with the condition, all sets has to display for the customer, according to the Total Rate the customer choose the suitable set.

[Rabbit]

Please use code tags when posting code or formatted data.

Is this a VB6 question or a SQL question? Your title says SQL but your post says VB6. Let me know so I can move this to the correct forum if needed.

Also, your explanation on how to achieve the results you want is very unclear. You don't define a definitive way to achieve the results you are showing.