SQL calculations 2

[heydyrtt]

I need to do a calculation of two fields, field1 / field2 then need to get the percentage of the result from the divide by 100%. Below is what I have written so far, which it works, but doesn't give the right percentage.

SELECT a.items, a.orders, c.items_credited, SUM(c.items_credited) / (a.items)-100
FROM credits c, invoice a
WHERE a.county_id = c.county_id
GROUP BY c.items_credited

Items will be a number like 2356, items credited by like 5, I'm dividing 5 from 2356 then -100, which is not giving the right results. Is there a better to do this to get the percentage.

Thanks







Heydyrtt

 

[carp]

I'm not sure why you are subtracting 100 from your division operation.

If you are trying to get a percentage, you would multiply by 100.

For example, if you are dividing 5/2356, your result would be 0.00212224108658743633276740237691. To convert this to a percentage, you would multiply by 100 to get 0.212224108658743633276740237691.

If this is what you are trying to do, then use:

SELECT a.items, a.orders, c.items_credited, 
(SUM(c.items_credited) / (a.items)) * 100
FROM credits c, invoice a
WHERE a.county_id = c.county_id
GROUP BY c.items_credited;

On the other hand, if I have misinterpreted your requirements, please let me know where I've gone astray!

Elbert, CO
1046 MDT

 

[heydyrtt]

Not what I needed, I'm looking to return a number like -99
It has substract from 100%. Items is the total number of items ordered. items_credited is the total number items left out of order. if there are no items_credited then they picked 100% of the order. If they leave items out then it will be less than 100%.

Heydyrtt

 

[carp]

You are asking for one thing and showing another!
You said "It has substract from 100%", but your code is "subtracting 100 from it".
Based on your notation that you want a negative number, I conclude you want to "subtract 100 from it".
So I think you want something like:

SELECT a.items, a.orders, c.items_credited, 
(SUM(c.items_credited)) / (a.items))*100 - 100
FROM credits c, invoice a
WHERE a.county_id = c.county_id
GROUP BY c.items_credited

In the previous example (rounding here for simplicity), if there are 2356 items, and 5 are credited, the above would give you:
(5/2356)*100 - 100 = -99.79

Is that what you are after?

 

[heydyrtt]

Yes sir, that got it


Thanks alot


Heydyrtt

 

[SantaMufasa]

Heydyrtt,

Carp probably spent 1/3 to 1/2 hour successfully dealing with your issue. At standard consulting rates, that's about $80 - $125 of free consulting to solve your problem. I'd say that a Purple Star was in order as a way of saying "Thank You". What do you think?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:44 (13Jul04) UTC (aka "GMT" and "Zulu"), 18:44 (12Jul04) Mountain Time)

 

[heydyrtt]

Yeah, he deserves a purple star.






Heydyrtt

 

[Dave29Palms]

Hey, Carp. This was a great post. Thanks!