summation of cost

[siva sankar]

Hi,

I have a table called Pallete as shown below



pallete_id category pallete_cost

2 Electronics 1000
4 Clothing 500
8 Hardware 2000
1 Clothing 1500
6 Hardware 4000
9 Electronics 4000
10 Electronics 1000
3 Clothing 500
7 Hardware 2000
5 Electronics 1000
13 Clothing 500
14 Hardware 2000
15 Clothing 1500
16 Electronics 1000
17 Hardware 2000
18 Electronics 1000
19 Clothing 500
20 Hardware 2000

As shown above there will be thousands of records.

What i want is 12 pallets of same category will be one 1 master pallete and cost of those 12 pallets shoulb be <=15000.

master pallete shuold contain exactly 12 palletes.this should not be less or greater.

can anyone suggest on how can i do this . its very urgent.

Thanks in Advance,
Best Regards,
Siva

[Paul Campbell]

Originally posted by siva sankar:
Hi,

I have a table called Pallete as shown below



pallete_id category pallete_cost

2 Electronics 1000
4 Clothing 500
8 Hardware 2000
1 Clothing 1500
6 Hardware 4000
9 Electronics 4000
10 Electronics 1000
3 Clothing 500
7 Hardware 2000
5 Electronics 1000
13 Clothing 500
14 Hardware 2000
15 Clothing 1500
16 Electronics 1000
17 Hardware 2000
18 Electronics 1000
19 Clothing 500
20 Hardware 2000

As shown above there will be thousands of records.

What i want is 12 pallets of same category will be one 1 master pallete and cost of those 12 pallets shoulb be <=15000.

master pallete shuold contain exactly 12 palletes.this should not be less or greater.

can anyone suggest on how can i do this . its very urgent.

Thanks in Advance,
Best Regards,
Siva

I can help, but I need you to clarify a couple of things... when you say 12 palletes... are you saying a pallete_id that has only 12 pallete categories or are you saying pick the pallete_id that has only 12 pallete categories with a sum cost of <= 15,000.

[siva sankar]

Hi Paul,

Here pallete_id is not a considerable thing.when the sum of costs of palletes are <=15000(the value should be close to 15000 in most cases),then the no.of palletes when added should be equal to 12.


Regards,
Siva

[Paul Campbell]

Originally posted by siva sankar:
Hi Paul,

Here pallete_id is not a considerable thing.when the sum of costs of palletes are <=15000(the value should be close to 15000 in most cases),then the no.of palletes when added should be equal to 12.


Regards,
Siva

SELECT pallete.category AS category,
SUM(pallete.pallete_cost) AS pallete_cost
FROM pallete
GROUP BY pallete.category
HAVING COUNT(pallete.category) = 12
AND SUM(pallete.pallete_cost) <= 15000

I may need to tweak this... I'm not 100% sure I'm understanding your requirements.
[ October 31, 2007: Message edited by: Paul Campbell ]