Embedding aggregate query in non-aggregate query

Question

I'm trying to create a database view which has the result of an aggregate query for a column value (see this post for the aggregate query used), e.g. something like

MESSAGEVIEW
----------------------------------------------
MESSAGEID    SENDER       PARTCOUNT        
----------------------------------------------
1            Tim          2
2            Bridgekeeper 0

I've tried this code for selecting the view data:

SELECT m.MESSAGEID, m.SENDER,
    (SELECT COUNT(mp.MESSAGEID)
         FROM MESSAGE m LEFT JOIN MESSAGEPART mp
         ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID) AS PARTCOUNT
    FROM MESSAGE m;

which returns ORA-01427: single-row subquery returns more than one row.

I've also tried

SELECT m.MESSAGEID, m.SENDER, COUNT(mp.MESSAGEID) AS PARTCOUNT
FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID 
GROUP BY m.MESSAGEID;

Which returns ORA-00979: not a GROUP BY expression.

How can I get a view which properly shows this information?

General remark, "not a GROUP BY expression." is probably Oracle way of telling yuo that you should put all non-aggregate fields in your group by in your second query, so m.SENDER should be there too... — oerkelens, Nov 21 '13 at 14:17

score 1 · Accepted Answer · answered Nov 21 '13 at 13:41

1

This should do it.

SELECT m.MESSAGEID, m.SENDER,
    (SELECT COUNT(mp.MESSAGEID)
         FROM MESSAGEPART mp
         WHERE mp.MESSAGEID = m.MESSAGEID) AS PARTCOUNT
    FROM MESSAGE m;

answered Nov 21 '13 at 13:41

jva

2,797
1
26
41

score 1 · Answer 2 · answered Nov 21 '13 at 13:58

You can do it with a subquery (see @Jva answer) or with the following JOIN:

SELECT m.MESSAGEID, m.SENDER,
       mp.PartCount AS PARTCOUNT

       FROM MESSAGE m
           LEFT JOIN 
             (SELECT MESSAGEID,COUNT(*) as PartCount 
                     FROM MESSAGEPART GROUP BY MESSAGEID) as mp 
             ON m.MESSAGEID=mp.MESSAGEID

score 1 · Answer 3 · edited Nov 21 '13 at 15:08

1

You can get it into a single query without a subquery if you use count() in window function format:

SELECT m.MESSAGEID, m.SENDER, COUNT(mp.MESSAGEID) OVER (PARTITION BY m.MESSAGEID) AS PARTCOUNT
FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID;

edited Nov 21 '13 at 15:08

Mureinik

297,002
52
306
350

answered Nov 21 '13 at 14:48

ajwatt

11
1

Embedding aggregate query in non-aggregate query

3 Answers3