the query is failing on a calculated field

[ajoo]

Hi all, 

 

The query below works great  

 

// This creates a temporary table 

CREATE TEMPORARY TABLE tmp_result AS
    SELECT xid FROM 
    (
        (
            SELECT mr.xid as xid
            FROM mr, ar 
            WHERE mr.mid = ar.xid && ar.aid = 94 
        )
        UNION 
        (
            SELECT ar.xid as xid
            FROM ar 
            WHERE ar.aid = 94 && ar.role <> 'master'
        ) 
    )tt;


// This finds the required record 

SELECT xid,bb.fname, bb.lname, bb.city, bb.cell, bb.email, bb.fid, bb.center_No 
from tmp_result 
INNER JOIN 
(
SELECT 
    fd.fname, fd.lname, fd.fran_id, fd.city, fd.cell, fd.email,
    sd.fid, sd.center_No 
From sd, fd 
WHERE sd.fid = fd.fran_id
) bb 
ON bb.fran_id = tmp_result.xid

and outputs the result shown in the attachment.

 

However if i wish to have a calculated field displayed as a part of the result, it returns an empty set.

 

Here is the one that returns the empty set with the simple addition ( count(fid) total )

SELECT xid,bb.fname, bb.lname, bb.city, bb.cell, bb.email, bb.fid, bb.center_No, bb.total 
from tmp_result 
INNER JOIN 
(
SELECT 
    fd.fname, fd.lname, fd.fran_id, fd.city, fd.cell, fd.email,
    sd.fid, sd.center_No, count(fid) total 
From sd, fd 
WHERE sd.fid = fd.fran_id
) bb 
ON bb.fran_id = tmp_result.xid

I have tried count(sd.fid) total  as well. Same result. 

 I have a similar query without a join and which does not use a temporary table where this and another calculated field works just fine. 

 

Kindly let me know how to solve this. 

 

Thanks all !

Edited August 10, 2016 by ajoo

[Barand]

Using an aggregation function (SUM(), COUNT() etc) without specifying a GROUP BY column(s) will return a single row containing the aggregate for the whole selection. Values for the other non-aggregated selected columns are indeterminate (although usually from the first record in the set.

[ajoo]

Guru Barand !!!!!!!!   

 

Thank you !