-1

I'm getting this message while using this query, is there anything wrong?

SELECT t.tanggal_transaksi, o.nama_lengkap, SUM(td.harga * td.qty) total
FROM transaksi t, transaksi_detail td, operator o
WHERE td.transaksi_id = t.transaksi_id AND o.operator_id = t.operator_id
GROUP BY t.transaksi_id

Updated : After using the answer from @Barbaros Özhan using this query :

SELECT t.tanggal_transaksi, o.nama_lengkap, SUM(td.harga * td.qty) total
  FROM transaksi t
 INNER JOIN transaksi_detail td ON ( td.transaksi_id = t.transaksi_id )
 INNER JOIN operator o ON ( o.operator_id = t.operator_id )
 GROUP BY t.tanggal_transaksi, o.nama_lengkap;

the data is successfully displayed. but, there are few problems that occur, the value of the same operator_id cannot appear more than 1 time. Here is the sample data :

+--------------+-------------+-------------------+
| TRANSAKSI_ID | OPERATOR_ID | TANGGAL_TRANSAKSI |
+--------------+-------------+-------------------+
|            1 |           5 | 09/29/2018        |
|            2 |           3 | 09/29/2018        |
|            3 |           3 | 09/29/2018        |
|            4 |           1 | 09/29/2018        |
|            5 |           1 | 09/29/2018        |
+--------------+-------------+-------------------+

After use the query command, the output is :

+-------------------+------------------+--------+
| TANGGAL_TRANSAKSI |   NAMA_LENGKAP   | TOTAL  |
+-------------------+------------------+--------+
| 09/29/2018        | Lina Harun       | 419800 |
| 09/29/2018        | Titro Kusumo     | 484000 |
| 09/29/2018        | Muhammad Kusnadi | 402000 |
+-------------------+------------------+--------+

When viewed from the operator table, there are 2 data with the same operator_id that is unreadable

+-------------+------------------+
| OPERATOR_ID |   NAMA_LENGKAP   |
+-------------+------------------+
|           1 | Muhammad Kusnadi |
|           3 | Lina Harun       |
|           5 | Tirto Kusumo     |
+-------------+------------------+
Kusnadi
  • 75
  • 1
  • 10
  • Normally I would just close this as a duplicate, because ORA-00979 is a common error and has plenty of answers on this site ([like this thread](https://stackoverflow.com/q/1520608/146325)). However, you seem to be having difficulty relating the given answer to your case, so please **edit your question** to provide more details. – APC Sep 29 '18 at 06:59
  • ok sir, thanks for the advice – Kusnadi Sep 29 '18 at 07:23
  • The purpose of aggregations is that they produce a result set with **one row per permutation** of aggregated columns. Your query groups by `t.tanggal_transaksi, o.nama_lengkap`. In your sample data the DATE is the same for all records, therefore the result set correctly has one row per OPERATOR. – APC Sep 29 '18 at 07:30
  • so how to display all data? – Kusnadi Sep 29 '18 at 07:36
  • It is not clear what you want. Please re-edit your question to include sample data from both tables and your required output from those sample records. – APC Sep 29 '18 at 10:46
  • oke i've already solved the issues, i want to seperate the records in table that i'm join, because the result it turns out that it combines data that has the same value, so it only displays one data with the same value. So, i'm add t.transaksi_id in group by. Here is the query : `SELECT t.tanggal_transaksi, o.nama_lengkap, SUM(td.harga * td.qty) total FROM transaksi t, transaksi_detail td, operator o WHERE td.transaksi_id = t.transaksi_id and o.operator_id = t.operator_id GROUP BY t.tanggal_transaksi, o.nama_lengkap, t.transaksi_id` – Kusnadi Sep 29 '18 at 11:30
  • If you want all the rows from table just remove the GROUP BY or the SUM(). – APC Sep 29 '18 at 11:35

1 Answers1

2

You need to include the columns in the SELECT-list t.tanggal_transaksi, o.nama_lengkap, also in the GROUP BY-list but not the others like t.transaksi_id. So, you might use the following without any issue :

SELECT t.tanggal_transaksi, o.nama_lengkap, SUM(td.harga * td.qty) total
  FROM transaksi t
 INNER JOIN transaksi_detail td ON ( td.transaksi_id = t.transaksi_id )
 INNER JOIN operator o ON ( o.operator_id = t.operator_id )
 GROUP BY t.tanggal_transaksi, o.nama_lengkap;

Or this one :

SELECT t.transaksi_id, SUM(td.harga * td.qty) total
  FROM transaksi t
 INNER JOIN transaksi_detail td ON ( td.transaksi_id = t.transaksi_id )
 GROUP BY t.transaksi_id;

P.S. Prefer using ANSI-92 JOIN standard rather than old-style comma-type JOIN.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • The parenthesis around the `ON` expression are unnecessary. – Andreas Sep 29 '18 at 03:36
  • but why is not get all the record? – Kusnadi Sep 29 '18 at 03:38
  • @Andreas I know but for I like so that it makes the appearence nicer – Barbaros Özhan Sep 29 '18 at 03:38
  • 1
    @Kusnadi All what records? Are there perhaps records with null `t.operator_id`? Or `transaksi` records without any `transaksi_detail` records? If so, then you need `LEFT JOIN` – Andreas Sep 29 '18 at 03:41
  • @Kusnadi INNER JOIN return only exact matches, if you need more records from all tables even non-matching columns (perhaps nulls as Andreas mentioned) you need to replace with `OUTER JOIN` like `LEFT OUTER JOIN`, `RIGHT OUTER JOIN` or `FULL OUTER JOIN` – Barbaros Özhan Sep 29 '18 at 03:44
  • the record is not show if have the same value of operator_id. Only show 1 record with different operator_id. **[like this](https://pasteboard.co/HG5Dmhn.png)**, why is not show even it have the same value of operator_id ? – Kusnadi Sep 29 '18 at 06:17
  • @Kusnadi - please **edit your question** to include sample data and required output derived from that sample data. Please use text not images, as not everybody can access image dumps. – APC Sep 29 '18 at 06:55