auurk auurk - 1 year ago 61
MySQL Question

Ordening search results per VAT rate

I have a table named invoiceItems. It contains individual lines of an invoice. It has the following fields:


  • invoiceItemId (primary key)

  • invoiceId (primary key of the table Invoices)

  • productId (primary key of the table products)

  • Rate

  • Quantity

  • vatRate (0%, 6%, 12%, 21%)



I want to query the database and select all invoiceItems with a particular invoice number. Then I want to echo out the different vatRates that appear in that invoice, and the total VAT amount per vatRate.

For example, if the results were:


  • invoiceItemId: 1, invoiceId: 17, productId: 23, Rate: 10 (€), Quantity: 10, vatRate: 6%

  • invoiceItemId: 2, invoiceId: 17, productId: 7, Rate: 100 (€), Quantity: 1, vatRate: 21%

  • invoiceItemId: 3, invoiceId: 17, productId: 8, Rate: 10 (€), Quantity: 5, vatRate: 12%



I would like to echo out:

INVOICE number 17

VAT 6% : € 6
VAT 12% : € 6
VAT 21% : € 21

$sql = "SELECT * FROM invoiceItems WHERE invoiceId = ’17’”;
$query = $connect->query($sql);
$result = $query->fetch_assoc();


I know I should loop through the results then, but I’m a bit stuck...

Answer Source

Use the GROUP BY and SUM operators build in in SQL.

    $sql = "SELECT vatRate, SUM(quantity * price * vatRate) as total FROM invoiceItems WHERE invoiceId = ’17’ GROUP BY vatRate”;
    $query = $connect->query($sql);

    while($result = $query->fetch_assoc()) { // Fetch for each vatrate
        // do what you want to do with the vats
        // use $result["vatRate"] and $result["total"]
    } 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download