Member Avatar for eetigu

hi guys,

i had an account on Daniweb but forgot password and recovery was a bit hectic so decided to crreate a new account. the previous username was emma etigu.

i have a problem and need some help. below i have two tables from my dataset, i would like to write an sql statement that will give me TOTAL SALES for the two tables combined. the tables contain related data derived from two different transaction processes.

receipDetals customerReceiptDetails
itemnumber itemnumber
itemname itemname
itemcount itemcount
purchaseprice purchaseprice
retailprice retailprice

these are two different tables, receiptDetails and customerReceiptDetails. i would like total quantity then total sales.
thanks.

  • 3 Contributors
  • 3 Replies
  • 284 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by moshelilin
Member Avatar for DaveAmour

I don't see any quantity or sales columns :(

What database is this coming from?

Do you want the SQL to populate your dataset in the first place, or are you wanting to sum things after the data is in a .net DataSet?

Member Avatar for eetigu

hi,

table1
itemName
itemCount
retailPrice
purchasePrice

table2
itemName
itemCount
retailPrice
purchasePrice

i would like to get combined total sales for both tables.

thanks.

Member Avatar for moshelilin 
SELECT SUM(cost) FROM
    (SELECT itemCount*purchasePrice as cost FROM table1
UNION ALL
    SELECT itemCount*purchasePrice as cost FROM table2)
Edited by Reverend Jim because: Corrected code formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.