How to group two joined tables

group byjoin;oracleunion

How can I group (by) two joined tables (in an Oracle database) like this:

cant:
id      CANT_VAL    COD_VAL fk_id_chest
18059   18.56       R12     1
18060   11          R10     2
18061   15          R11     3

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest
18059   18.56       R12        1
18059   1134        R10        1
18059   1234        R3         1
18060   11          R10        2
18061   15          R11        3
18061   1111        R5         3

fk_id_chest is a "link" to another table that I need in the final result.
Thanks!

Best Answer

I found my answer, thanks!

select cant.id,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST from cant
union
select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT.FK_ID_CHEST from col 
    inner join cant
        on cant.id=COL.FK_ID_COL_DMA

PROPOSED QUERY

SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
    id int not null auto_increment,
    time datetime,
    country varchar(20),
    PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE hits
    -> (
    ->     id int not null auto_increment,
    ->     time datetime,
    ->     country varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hits (time,Country) VALUES
    -> ('2014-06-19 01:00:00','France'),
    -> ('2014-06-19 01:00:00','Brazil'),
    -> ('2014-06-19 01:00:00','USA'),
    -> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time                | country |
+----+---------------------+---------+
|  1 | 2014-06-19 01:00:00 | France  |
|  2 | 2014-06-19 01:00:00 | Brazil  |
|  3 | 2014-06-19 01:00:00 | USA     |
|  4 | 2014-06-19 02:00:00 | USA     |
+----+---------------------+---------+
4 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    ->     COUNT(1) TotalHits
    -> FROM
    -> (
    ->     SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    ->     FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
    -> ) AA
    -> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic               | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00     |         1 |
| Total for Brazil        |         1 |
| 2014-06-19 01:00:00     |         1 |
| Total for France        |         1 |
| 2014-06-19 01:00:00     |         1 |
| 2014-06-19 02:00:00     |         1 |
| Total for USA           |         2 |
| Total for All Countries |         4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

I'll leave the formatting to you :-)

CAVEAT: Please make sure you have a time index on the table

ALTER TABLE hits ADD INDEX time_index (time);

An additional query could be

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;

whose output would be

+-------------------------------+-----------+
| Statistic                     | TotalHits |
+-------------------------------+-----------+
| Brazil                        |         1 |
| France                        |         1 |
| USA                           |         1 |
| Total for 2014-06-19 01:00:00 |         3 |
| USA                           |         1 |
| Total for 2014-06-19 02:00:00 |         1 |
| Total for 2014-06-19          |         4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)

UPDATE 2014-06-21 19:07 EDT

Perhaps the the two queries combined with UNION will work for you

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

I removed the WITH ROLLUP from the first one so that the total comes out once

Oracle – Inner Join Select with Group By

There are several ways to do this. Either remove the join and convert the subqueries to correlated:

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b
     WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS PET3,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS  b
     WHERE b.TIP = 'ALT' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS ALT3
FROM ANEXA2A_TABEL1  a ;

or keep the join and use GROUP BY (I converted to LEFT join so you also see results for values that the second table has no related rows):

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    SUM(CASE WHEN b.TIP = 'PET' THEN b.CANT ELSE 0 END) AS PET3,
    SUM(CASE WHEN b.TIP = 'ALT' THEN b.CANT ELSE 0 END) AS ALT3
FROM ANEXA2A_TABEL1  a
    LEFT JOIN ANEXA2A_TABEL2_ROWS  b
        ON b.FK_ID_ANEXA = a.FK_ID_ANEXA 
GROUP BY 
    a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2 ;

There are several more options to do this (i.e. first group by table b in a derived table and the join to table a, or using PIVOT, or using the specific to Oracle function DECODE instead of CASE, ...)