Adding up item prices to get total

Question

I have started making a database and I have an 'ordered item' and a 'invoice' table. I would like to find out how I would add up the prices of the items that the customer picks. As well where abouts do you put this 'rule' on the database.

Answer 1

You want the SQL "Sum()" function. Look, for example, here.

Answer 2

Basically you would make a query and populate the invoice table with the results of that query. As Coderer said, use a sum() function. If you are using the graphical query editor you will need to enable totals (View->Totals) and then set the total type to "Sum" instead of the default "Group By"

Answer 3

If you are using MS Access then typically the SUM function is used as part of a report for one of the displayed fields.

Answer 4

I have to make a lot of assumptions (biggest being you're using MS SQL) because your post lacks detail, but ill take a stab. If this setup is not close, please post table creates and some sample data for us to work with. It will help us a lot.

declare @Item table (ItemId int, ItemDesc varchar(50), Price money)
declare @Invoice table (InvoiceId int, CustomerName varchar(50), ShipDate datetime)
declare @OrderedItem table (InvoiceId int, ItemId int, Quantity int)


insert into @Item
    select 1, 'Hat', '10.99' union
    select 2, 'Shirt', '12.50' union
    select 3, 'Belt', '5.99'

insert into @Invoice
    select 1, 'Nathan', '01-01-2009'

insert into @OrderedItem
    select 1, 1, 2 union -- 2 hats
    select 1, 2, 3   -- 3 shirts


select  i.InvoiceId,
     sum(Quantity) [TotalItems],
     sum(Price * Quantity) [TotalPrice]
from    @Invoice i
inner
join    @OrderedItem oi on
     i.InvoiceId = oi.InvoiceId
inner
join    @Item it on
     oi.ItemId = it.ItemId
group
by   i.InvoiceId

Answer 5

SQL the language has but one data structure being the table. Therefore, you could put the items the user has picked into a table then JOIN from this table to the table containing the price then use the set function SUM() to get the total price. Sounds like you are not too familiar with SQL the language and if you are indeed using MS Access then it has wizards to help: it calls this kind of construct a 'totals query'.

Answer 6

You will find a lot of useful examples in the Northwind sample database that ships with every version of Access, it can also be downloaded from here. I think you will find the order form, the sales analysis form and the invoice report of particular intrest.