MySQL multiple table joins and multiple data levels – best practice

join;MySQL

I have multiple tables i wish to join up to produce one big query with specific columns summed.
I was having a problem getting the correct summed columns in the final query
(i believe this was because i was due to data being joined to many lines and summing after the join)

is the correct way to handle this type of problem to join on select statements?
This is the way i achieved it but wasnt sure if this is the correct method?

select 
    t1.id_a,
    sel_t2.sum_mc
from xyz as t1
    left join
        (select id_a, sum(occ) as sum_mc
        from t2
        group by id_a) as sel_t2
    on t1.id_fh = sel_t2.id_a
group by t1.id_a;

Best Answer

See if this gives you the correct values:

SELECT t1.id_a, 
        ( SELECT  sum(occ)
            from  t2
            WHERE  t2.id_a = t1.id_fh
            group by  t2.id_a 
        ) AS sum_mc
    FROM xyz AS t1

Note: I am assuming that t1.id_a is unique, so that the GROUP BY is not needed.

(It is confusing to have id_a in both tables, yet not be JOINing on it.)

Related Solutions

MySQL – SUM with Multiple GROUP BY on Same Table

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types.

For example:

SELECT
    idclient AS [Client ID]
   ,COUNT(value) AS [Total Orders]
   ,SUM(value) AS [Total Order Value]
   ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' THEN value ELSE 0 END) [Order Combo 2]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' AND type3 = 'another type' THEN value ELSE 0 END) [Order Combo 3]
FROM 'test'.'orders'
GROUP BY idclient

This will group the orders on [idclient] and will SUM([value]) the instances where certain order records meet the following conditions or combinations you specified.

So, if a customer has an order where [type1] was Apples and [type2] was Oranges, you can SUM([value]) that was 1 under a new column called something like [Apple/Orange Orders].

The result can also include additional combinations for the same record in multiple fields/columns. If that is a problem, just move the CASE to the GROUP BY to break each combination into a separate record in the the result set.

Hope this helps!

Join multiple Tables by date

You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) 
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY 
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount) 
              FROM returns  r 
              WHERE r.dat = d.dat
            ), 0) 
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount) 
              FROM orders  o 
              WHERE o.dat = d.dat
            ), 0) 
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY 
  d.dat ;

Best Answer

Related Solutions

MySQL – SUM with Multiple GROUP BY on Same Table

Join multiple Tables by date

Related Question