Jump to content

the query is failing on a calculated field


ajoo
Go to solution Solved by Barand,

Recommended Posts

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 !

post-146548-0-96919100-1470814961_thumb.png

Edited by ajoo
Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.