#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    Sum up invoices based on zip and tax amount, pulling my hair out


    Hello,

    I have a database of invoices and clients. The clients are stored in a table called clients along with their zip code.

    The invoices are stored on a separate table.

    I have a field for the amount of sales tax I have collected for each invoice.

    I need to select with one query the following grouped by unique zip codes:

    sum(invoices.tax) for all invoices
    sum(invoices.item_totals) for all invoices
    sum(invoices.item_totals) for ONLY invoices that have zero tax
    sum(invoices.item_totals) for ONLY invoices that HAVE tax>0

    Basically I need to determine how much the total revenue was, how much sales tax I collected, how much of the revenue had a tax and how much revenue did not have tax, all grouped by unique zip codes.

    So I would have as the result:

    Zip-Nontaxable-taxable-collected
    12345-500-250-20.75

    My current query which is not working, I cant seem to figure out how to have sub queries group data based off different conditions then the parent select statement:
    Hello,

    I have a database of invoices and clients. The clients are stored in a table called clients along with their zip code.

    The invoices are stored on a separate table.

    I have a field for the amount of sales tax I have collected for each invoice.

    I need to select with one query the following grouped by unique zip codes:

    sum(invoices.tax) for all invoices
    sum(invoices.item_totals) for all invoices
    sum(invoices.item_totals) for ONLY invoices that have zero tax
    sum(invoices.item_totals) for ONLY invoices that HAVE tax>0

    Basically I need to determine how much the total revenue was, how much sales tax I collected, how much of the revenue had a tax and how much revenue did not have tax, all grouped by unique zip codes.

    So I would have as the result:

    Zip-Nontaxable-taxable-collected
    12345-500-250-20.75

    My current query which is not working, I cant seem to figure out how to have sub queries group data based off different conditions then the parent select statement:

    SELECT
    clients.zip AS zips,
    sum( "magic to sum all invoices" ) AS all_income,
    sum( "magic to sum invoices that dont have tax" ) AS nontaxable_income,
    sum( "magic to sum invoices with a tax" ) AS taxable_income,
    sum( invoices.tax ) AS collected_tax
    FROM invoices
    JOIN clients ON clients.id = invoices.client_id
    WHERE
    YEAR( invoices.date ) = '2013'
    GROUP BY zips
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    to only sum up the totals meeting a certain criterion, have the other totals yield 0:

    sql Code:
    SUM(CASE WHEN <tax condition> THEN invoices.item_totals ELSE 0 END)
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by Jacques1
    ... have the other totals yield 0:
    or NULL

    Code:
    SUM(CASE WHEN <tax condition> THEN invoices.item_totals END)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo