Help with Table Combination

[jasonstewart]

Look at this example of an invoice 3000(ServiceRecordID) that I have to combine the fields and totals including tax and then put them into a seperate table but I can't seem to figure the correct SQL to do this.

This is the example of invoice #3000


SourceRecordID Category Rate Qty
3000 P 135.00 2
3000 P 23.56 4
3000 L 65.00 5
3000 P 12.00 2
3001 L 65.00 8

I have the ability to turn that information into another table that is then layed out like this

SourceRecordID Category InvoiceTotal
3000 P 388.24
3000 L 325.00
3001 L 520.00

I need to add a fixed sales tax(0.07616) to the parts column, represented by (P), and add the total to another table, which already exists, that will look like this

SourceRecordID TotalParts TotalLabor
3000 417.81 325.00

That is what I need the finished table to look like

I have mentioned this issue in a previous thread. But I have yet to resolve this issue. Thank you for all of anyones help.

 

[gol4]

use subquery and alias the tables

replace table1 with the name of your table and fix my spellin errors and something like this should do it

select SourceRecordID,(select (a.rate * a.qty)*.07616 from table as a where a.catagory = "P" and a.sourcerecordid = table1.sourcerecordid ),(select b.rate * b.qty from table1 as b where b.catagory = "P" and b.sourcerecordid = table1.sourcerecordid )
from table1
group by sourcerecordid

You could also use dsum()

good luck

 

[gol4]

too many errors above
Needs to be more like this

select SourceRecordID,(Select (a.rate * a.qty)*.07616 from table as a where a.catagory = "P" and a.SourceRecordID = table1.SourceRecordID group by SourceRecordID )as TotalParts,(select b.rate * b.qty from table1 as b where b.catagory = "L" and b.sourcerecordid = table1.sourcerecordid group by SourceRecordID) as TotalLabor
from table1
group by sourcerecordid

 

[jasonstewart]

I keep getting an error - invalid expression in an aggregate function.

This is what my SQL looks like

SELECT ServiceRecordID, (Select ([Items].UnitPrice * [Items].Qty)*.07616 FROM Items AS a WHERE a.Category = "P" and a.ServiceRecordID = Items.ServiceRecordID GROUP BY ServiceRecordID ) AS TotalParts,
(Select(b.UnitPrice * b.Qty) FROM Items AS b WHERE b.Category = "L" and b.ServiceRecordID = Items.ServiceRecordID GROUP BY ServiceRecordID) AS TotalLabor
FROM Items
GROUP BY ServiceRecordID;

What am I doing wrong??

 

[gol4]

Sorry Jason
I promise I will not post before I have had my first cup of coffee. The group by is not required in the subselects

I did a quick mock up and here is my working code

SELECT ITEMS.RECORDID, (SELECT SUM(RATE * QUANTITY) + SUM( RATE * QUANTITY * .07616) FROM ITEMS AS A WHERE A.RECORDID = ITEMS.RECORDID AND A.CATAGORY = "P" ) AS PARTS, (SELECT SUM(RATE * QUANTITY) FROM ITEMS AS A WHERE A.RECORDID = ITEMS.RECORDID AND A.CATAGORY = "L" ) AS LABOR
FROM ITEMS
GROUP BY ITEMS.RECORDID;

 

[jasonstewart]

THANK YOU !!! THAT DOES WORK AND I HAVE A FEW OTHER PROBLEMS BUT I AM DETERMINED TO FIGURE THEM OUT ON MY OWN. I REALLY APPRECIATE THE TIME SPENT AND YOUR HELP.