help getting query work

Discussion in 'Databases' started by neilfurry, Jul 17, 2016.

0
  1. #1
    hi there,
    can i ask some help please.

    i need to get the addon percentage on my database... add on is being computed based on the difference between an invoice and estimate.

    my query goes like this:

    SELECT
    COUNT(estimate) as est,
    SUM(invoice-estimate) as addons,
    SUM(addons / est) * 100 as Percentage
    FROM tblestimates

    What is the best way to do this? i know that i cant use aliases on the computation i made.
    Thank you
     
    neilfurry, Jul 17, 2016 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #2
    It's quite useful to create a "fiddle" for these kinds of things

    check out http://sqlfiddle.com/#!9/f8c3c/2

    I've basically created 2 queries
    select `id`
  , `invoice`-`estimate` as `addons`
  , ((`invoice`-`estimate`)/`estimate`)*100 as `percentage`
from `tblestimates`
    Code (markup):
    This checks that the basic premise is right and is going to give you the data on a row by row basis that you are looking for

    select count(`id`)
  , sum(`invoice`-`estimate`) as `addons`
  , (( sum(`invoice`)-sum(`estimate`))/sum(`estimate`))*100 as `percentage`
from `tblestimates`
    Code (markup):
    You have to be really careful about percentages - I'm sure you can find something more elegant than this but this will do the trick
     
    sarahk, Jul 17, 2016 IP