# Doing Several Math Operations During A SELECT? - MySQL

Hi there I have a table like so: cID | jID | Unit | Rate | ------------------------- 1 1 5 250 2 1 4 220 3 2 5 160 4 3 3.5 190 5 4 4 250 6 4 5 250 What I ultimately want to do is get a total cost (Unit * Rate) for each jID. So, the total cost for jID 1 would be (5 * 250) + (4 * 220) = 2130; the total cost for jID 2 would be (5 * 160) = 800; jID 3 would be (3.5 * 190) = 665; and jID ...

1. ## Doing Several Math Operations During A SELECT?

Hi there

I have a table like so:

cID | jID | Unit | Rate |
-------------------------
1 1 5 250
2 1 4 220
3 2 5 160
4 3 3.5 190
5 4 4 250
6 4 5 250

What I ultimately want to do is get a total cost (Unit * Rate) for each
jID.

So, the total cost for jID 1 would be (5 * 250) + (4 * 220) = 2130; the
total cost for jID 2 would be (5 * 160) = 800; jID 3 would be (3.5 * 190)
= 665; and jID 4 would be (4 * 250) + (5 * 250) = 2250.

Can I get these total amounts via one query? ie: achieve a result like:
jID | Total
-----------
1 | 2130
2 | 800
3 | 665
4 | 2250

Right now I'm just pulling *all* the rows and using PHP to calculate
totals costs, but I was wondering if I could do it at the MySQL level?
Obviously I would never know how many rows share the same jID...

Can someone please take a moment to tell me if my goals are possible
using a single query to give me my results? And if so, what might the
SELECT query be?

Good Man Guest 2. ## Re: Doing Several Math Operations During A SELECT?

Good Man <heyholetsgo.com> wrote in
news:Xns97A2964EF5A98sonicyouth216.196.97.131:
> Can I get these total amounts via one query? ie: achieve a result
> like: jID | Total
> -----------
> 1 | 2130
> 2 | 800
> 3 | 665
> 4 | 2250
i worked it out, here's how to do it (i think):

SELECT jID,SUM(Rate * Unit) as Total FROM JobCosts GROUP BY jID ORDER BY
jID

Good Man Guest #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•