Calculate an average with 2 rows of 2 different tables

user_15982699 0
Viewed 40

I'm quite new to MySQL and currently I'm struggling with a problem.

I have 2 Tables (techlog and sales). What I intend to do now is, get a sum out of a column in techlog and the same with sales. These two sums I'd need to calculate something new Sum(sales.row) / Sum(techlog.row). The idea for this is that I could get a Price per Minute. Is something like that possibe?

Both tables share column with the same ID. Further some Columns such as the Immatriculation is also available on both tables.

What I've tried so far:

use dbm_project;

SELECT techlog.Immatriculation, sum(techlog.TimeTOT), sum(sales.total)
FROM techlog
INNER JOIN sales
ON techlog.Immatriculation = sales.Immatriculation
GROUP BY techlog.Immatriculation

My apologies in advance since I find it hard to formulate my problem with missing experience.

Long story short: I want to calculate two values of two different tables and with those I want to calculate something new.

subquery aggregate-functions group-by mysql
1 Answers

This is a job for subqueries.

One is

         SELECT Immatriculation, sum(TimeTOT) TimeTot
           FROM techlog
          GROUP BY Immatriculation

The other is

         SELECT Immatriculation, sum(total) SalesTot
           FROM sales
          GROUP BY Immatriculation

Then you join them together

SELECT t.Immatriculation, t.TimeTot, s.SalesTot
  FROM (
             SELECT Immatriculation, sum(TimeTOT) TimeTot
               FROM techlog
              GROUP BY Immatriculation
       ) t
  LEFT JOIN (
             SELECT Immatriculation, sum(total) SalesTot
               FROM sales
              GROUP BY Immatriculation
       ) s ON t.Immatriculation = s.Immatriculation;

This works because ....

  1. Subqueries can take the place of tables. That's the S in Structured Query Language.
  2. Each of the two subqueries has one row per Immatriculation value. So you get one row in your result set for each.
user_2056080
Related