Writing a query to get a total

[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

 

[GreySky]

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

 

[Dale Fye]

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