Writing a query to get a total

  • Thread starter Thread starter Tracy
  • Start date Start date
T

Tracy

How do I write a select statement that will return a total
from a column with a condition? This is the situation I
have an invoice table. The items which are taxable have a
invoice.detail_type="M". I want to be able to run a query
that for any given invoice returns the total amount in $
of the line items that are type "M" and those that are not.
Invoice 30538 has 7 line items, 5 of which are
detail_type "M" for a total of 575.35, the other 2 line
items total 387.23. I want my query to return just those
two numbers.

Thanks in advance
 
You will use aggregation to perform this. You will Group
By on your Invoice, Sum on the Amount, and -- critically --
Group By on an expression that answers "Yes or No" to the
question: Is this Type M?

The SQL might look something like this (air SQL):

SELECT Invoice, detail_type='M' As IsTaxable,
Sum(Amount) As TotalAmount
FROM tblInvoices
WHERE Invoice = [YourInvoiceNumber]
GROUP BY Invoice, detail_type='M'

David Atkins, MCP
 
You don't indicate how you want those to numbers. This method will
create a single record for each invoice# with a column for Taxable and
NonTaxable values.

SELECT InvoiceNum
, SUM(IIF(Detail_type = "M", Amount, 0)) as Taxable
, SUM(IIF(Detail_Type= "M", 0, Amount)) as NonTaxable
FROM Invoice
GROUP BY InvoiceNum

--
HTH

Dale Fye


How do I write a select statement that will return a total
from a column with a condition? This is the situation I
have an invoice table. The items which are taxable have a
invoice.detail_type="M". I want to be able to run a query
that for any given invoice returns the total amount in $
of the line items that are type "M" and those that are not.
Invoice 30538 has 7 line items, 5 of which are
detail_type "M" for a total of 575.35, the other 2 line
items total 387.23. I want my query to return just those
two numbers.

Thanks in advance
 
Back
Top