Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: At most one record can be returned by this subquery. (Bug 3354)

    Hi,

    With the following table structure


    Availablity

    Availability_Id Availablity_Date
    1 22/10/2009
    2 22/10/2009


    Availability_details

    Availability_det_Id Availability_Id Time Process_available
    1 1 22:10 True
    2 1 22:20 True
    3 2 22:20 True
    4 1 22:30 False
    5 2 22:30 True


    Expected OutPut

    Availablity Id Available %
    1 66%
    2 100%

    When i try with the following query,

    SELECT availability_id, round (
    ( SELECT COUNT(*) FROM AVAILABILITY_DETAILS WHERE PROCESS_AVAILABLE = TRUE group by availability_id) /
    (SELECT COUNT(*) FROM AVAILABILITY_DETAILS group by availability_id) * 100,1) AS PERCENTAGE FROM
    FROM Availability_Details

    i am getting "At most one record can be returned by this subquery. (Bug 3354)" error.

    Kindly help me out with this Query

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by srian
    Hi,

    With the following table structure


    Availablity

    Availability_Id Availablity_Date
    1 22/10/2009
    2 22/10/2009


    Availability_details

    Availability_det_Id Availability_Id Time Process_available
    1 1 22:10 True
    2 1 22:20 True
    3 2 22:20 True
    4 1 22:30 False
    5 2 22:30 True


    Expected OutPut

    Availablity Id Available %
    1 66%
    2 100%

    When i try with the following query,

    SELECT availability_id, round (
    ( SELECT COUNT(*) FROM AVAILABILITY_DETAILS WHERE PROCESS_AVAILABLE = TRUE group by availability_id) /
    (SELECT COUNT(*) FROM AVAILABILITY_DETAILS group by availability_id) * 100,1) AS PERCENTAGE FROM
    FROM Availability_Details

    i am getting "At most one record can be returned by this subquery. (Bug 3354)" error.

    Kindly help me out with this Query

    Thanks
    Hi

    I think I would try something like this
    Code:
    SELECT availability_id, round(Sum(IIF(PROCESS_AVAILABLE = TRUE,1,0))/COUNT(availability_id) * 100,1) AS [PERCENTAGE FROM] FROM Availability_Details GROUP BY availability_id
    I'm sure there are probably better ways (anybody!) but it should work??

    MTB

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Hello MTB,

    Thanks a lot, I got extactly what i was looking for.. Many thanks.

    Moreover, i was trying something like this, this also works but u'r solution looks very simple.


    SELECT AVAILABILITY_DETAILS.Availability_id,Process.Proce ss_name ,
    (Select count(*) from Availability_Details where Process_available = true and AVAILABILITY_DETAILS.Availability_id = Availability.Availability_id ) as available,
    Count(*) AS Total,
    Round(available /Total * 100 ,1) as Percentage
    FROM AVAILABILITY_DETAILS , Availability,Process where AVAILABILITY_DETAILS.Availability_id = Availability.Availability_id
    and Availability.process_id = Process.Process_id
    group by AVAILABILITY_DETAILS.Availability_id,
    Availability.Availability_id,
    Process.Process_name;


    Thanks

    Regards

    Srian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •