Reporting, such as income / loss, etc.

Question

I have 2 tables, purchases and sales, each of which has a date field and a cost field.

I want to generate reports from several different types, for example, I want to show profit and loss for each month or year or show how often a certain item is sold for a given arbitrary period of time (probably based on user input)

Is it really difficult to make queries like these, do they rely on complex calculations?

For example, how would I summarize the sales.costs field and the total number of purchases. Dice to show profit and loss?

Answer 1

, , SQL . - :

SELECT SUM(purchases.costs) + SUM(sales.costs) AS total_cost FROM purchases, sales;

, , - :

SELECT SUM(sales.cost) + SUM(purchases.cost) AS cost,
       YEAR(sales.ts) AS year 
  FROM sales INNER JOIN purchases 
       ON YEAR(sales.ts) = YEAR(purchases.ts)
  GROUP BY YEAR(sales.ts);