How to unify two SQL queries

Question

I have two SQL queries, both of them are working separately, but when I try to unify hem, I have only problems

Query 1 :

SELECT Day( funct_consumatori.timestamp ) AS numar_zi, ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) AS ore_functionare, ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) AS consum, ROUND( ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) * pret_energie.pret, 2 ) AS estimare
FROM funct_consumatori
INNER JOIN consumatori ON consumatori.id = funct_consumatori.consumator
INNER JOIN pret_energie ON pret_energie.id =1
WHERE Year( funct_consumatori.timestamp ) = Year(
CURRENT_TIMESTAMP )
AND Month( funct_consumatori.timestamp ) = Month(
CURRENT_TIMESTAMP )
GROUP BY numar_zi DESC

Query 2:

SELECT Day( ambienta.timestamp ) AS numar_zi, TRUNCATE( AVG( temperatura ) , 1 ) AS temp_med, MIN( temperatura ) AS temp_min, MAX( temperatura ) AS temp_max, TRUNCATE( AVG( umiditate ) , 1 ) AS umid_medie
FROM ambienta
INNER JOIN consumatori AS c2 ON c2.id = '1'
INNER JOIN pret_energie AS pe ON pe.id = '1'
WHERE Year( ambienta.timestamp ) = Year(
CURRENT_TIMESTAMP )
AND Month( ambienta.timestamp ) = Month(
CURRENT_TIMESTAMP )
GROUP BY numar_zi DESC

How can I unify them based on numar_zi(INNER one)?

Possible duplicate of: http://stackoverflow.com/questions/10538539/join-two-select-statement-results — Galz, Aug 30 '16 at 23:51

score 2 · Accepted Answer · answered Aug 31 '16 at 01:39

I don't know your table and data but following SQL might work

select q1.numar_zi
        ,q1.ore_functionare
        ,q1.consum
        ,q1.estimare
        -- value of q2 start 
        ,q2.numar_zi
        ,q2.temp_med
        ,q2.temp_min
        ,q2.temp_max
        ,q2.umid_medie
from 
    (
    SELECT Day( funct_consumatori.timestamp ) AS numar_zi
    , ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) AS ore_functionare
    , ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) AS consum
    , ROUND( ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) * pret_energie.pret, 2 ) AS estimare
    FROM funct_consumatori
    INNER JOIN consumatori ON consumatori.id = funct_consumatori.consumator
    INNER JOIN pret_energie ON pret_energie.id =1
    WHERE Year( funct_consumatori.timestamp ) = Year(
    CURRENT_TIMESTAMP )
    AND Month( funct_consumatori.timestamp ) = Month(
    CURRENT_TIMESTAMP )
    GROUP BY numar_zi DESC
    ) q1

INNER JOIN

    (
    SELECT Day( ambienta.timestamp ) AS numar_zi
    , TRUNCATE( AVG( temperatura ) , 1 ) AS temp_med
    , MIN( temperatura ) AS temp_min
    , MAX( temperatura ) AS temp_max
    , TRUNCATE( AVG( umiditate ) , 1 ) AS umid_medie
    FROM ambienta
    INNER JOIN consumatori AS c2 ON c2.id = '1'
    INNER JOIN pret_energie AS pe ON pe.id = '1'
    WHERE Year( ambienta.timestamp ) = Year(
    CURRENT_TIMESTAMP )
    AND Month( ambienta.timestamp ) = Month(
    CURRENT_TIMESTAMP )
    GROUP BY numar_zi DESC
    ) q2
on
    q1.numar_zi = q2.numar_zi

Please make the change according to your RDMS(if necessary), but remember if you want to join two query use the following query

select t1.Id, t1.Col1, t1.Col2, t2.Id, t2.Col1, t2.Col2 
from 
    (SELECT Id, COUNT(*) AS Col1, Col2 FROM Table2 GROUP BY Id) t1
INNER JOIN -- or, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN .... 
    (SELECT Id, COUNT(*) AS Col1, Col2 FROM Table2 GROUP BY Id) t2
on
    t1.Id= t2.Id

Code is working well but the problem is that I have only 2 rows, in fact the `INNER` query from table `ambienta` has 2 results and the `funct_consumatori`query has 31 (last days of current month) so what can i do to take all data from the table with complete data for every day and if in other table exist data for same day, to assign to current table? — Marcel Domuta, Aug 31 '16 at 07:02
It is ok, I had to just use `RIGHT JOIN` and some small modifications, thanks alot Raihan, You have my upvote! — Marcel Domuta, Aug 31 '16 at 07:19

How to unify two SQL queries

1 Answers1