Search code examples
sqloracle-databaseoracle9i

Oracle 9.i SELECT FROM two table and three record to result one record

I have the following queries (query 1):

SELECT AVG(a.nnilai)*75/100 
FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'

The Result of Query 1: click for preview

(query 2):

    SELECT t.nbobot FROM TRNILAIKINERJA t 
WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT'

The Result of Query 2: click for preview

(query 3):

SELECT p.nbobot FROM TRNILAIKINERJA p 
WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM'

The result of Query 3: click for preview

So far, I have run this query:

SELECT AVG(a.nnilai)*75/100,
(SELECT t.nbobot FROM TRNILAIKINERJA t WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT'),
(SELECT p.nbobot FROM TRNILAIKINERJA p WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM')
 FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'

But It's result:

ORA-00937: not a single-group group function

The complete record (*) from the queries:

SELECT * 
FROM trangketmhs a 
WHERE cthajar='20172018' 
  AND a.csmt='E' 
  AND a.kddos='00084'

click for preview

(2) and (3) 

SELECT * 
FROM TRNILAIKINERJA t 
WHERE t.ckddos='00084' 
  AND t.cthajar='20172018' 
  AND t.csmt='E'

click for preview

My Question

How to make the result of 3 queries become this output:

| AVG(a.nnilai)*75/100  | NBOBOT1  | NBOBOT2 |
|-----------------------|----------|---------|
|     2,58818181818182  |     0.6  |     0.4 |
Solution

  • Simpler alternative to my previous answer:

    SELECT a.the_average,
    ,(SELECT t.nbobot FROM TRNILAIKINERJA t WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT') nbobot1
    ,(SELECT p.nbobot FROM TRNILAIKINERJA p WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM') nbobot2
FROM (
  SELECT AVG(a.nnilai)*75/100 the_average
    FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'
) a