Professional Web Applications Themes

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. #1

    Default 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?

    Thanks in advance.
    Good Man Guest

  2. #2

    Default 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

Similar Threads

  1. CF 6.1 is wrong when performing BASIC math operations!!
    By LuigiPia in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: March 24th, 07:40 PM
  2. math problem will Math.floor help
    By javman webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: February 4th, 01:28 AM
  3. File Operations
    By Cameron in forum PHP Development
    Replies: 1
    Last Post: January 8th, 02:01 PM
  4. Replies: 4
    Last Post: December 19th, 07:09 AM
  5. Database Operations
    By chadl webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 25th, 05:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139