1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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,500
    Likes Received:
    4,460
    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