CODEWITHSUNDEEP
sql-server
Caetano
Caetano

Reputation: 1

Inner join with the same table

I have this DB based on orders, split by products (if an order had 2 products, then there are 2 rows with the same transaction code but different products). It records when a product was acquired and when it was sold (basically, when it got in and when it got out). The cost of the product is correct only when it was acquired (a system problem messes with cost when product is sold).

I want to build a query with the following columns:

  1. Transaction code (only selling orders)

  2. Cost of the product when it was acquired - grouped by transaction code (one row for each transaction).

Can you help me with that? There is a column that states if the transaction was an acquisition or sale, so it can be used.

So basically: "server, bring me the selling transactions, and sum the cost of the products related to it, but their acquisition cost"

Upvotes: 0

Views: 54

Answers (1)

Tyron78
Tyron78

Reputation: 4187

Just guessing, but worth a try:

SELECT transaction_id, sum(product_cost) AS cost
  FROM table
  WHERE flag = acquisition
  GROUP BY transaction_id

Upvotes: 1

Related Questions