Generating reports, such as income/loss etc

Question

I have 2 tables, purchases and sales with each having a date field and a cost field.

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

Is it terribly complex to do queries such as this, do they rely on complex calculations?

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

Answer 1

This sort of thing is fairly straight forward, and an excellent example of why SQL databases are powerful and fun to use. For your example query, I'd do something like this:

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

To do a query that gives you cost by year, you could do something like this:

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);