Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    6

    Cool Unanswered: subquery returned more than 1 value ???????

    Hi !
    i m getting the error - ' subquery returned more than 1 value" in my following code -
    SELECT DISTINCT Dt_From, Dt_to, COUNT(DISTINCT Work_Code) AS NO_OF_WORKS,
    (SELECT COUNT(DISTINCT Work_Code) AS Expr1
    FROM RJ24Musterrollmaster1011
    GROUP BY Dt_From, Dt_to, wg_list_prepared
    HAVING (Dt_From >= '1/1/2011') AND (wg_list_prepared = 'y') AND (Dt_to <= '1/30/2011')) AS Expr1
    FROM RJ24Musterrollmaster1011 AS RJ24Musterrollmaster1011_1
    GROUP BY Dt_From, Dt_to
    HAVING (Dt_From >= '1/1/2011') AND (Dt_to <= '1/30/2011')
    ----------------------------------------------------------------------
    when i execute both the query separately then both the query return same no. of records
    If any one helps me it will be thankful.............

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Dt_From
         , Dt_to
         , COUNT(DISTINCT Work_Code) AS NO_OF_WORKS
         , COUNT(CASE WHEN wg_list_prepared = 'y'
                      THEN wg_list_prepared
                      ELSE NULL END) AS Expr1
      FROM RJ24Musterrollmaster1011 
     WHERE Dt_From >= '1/1/2011'
       AND Dt_to <= '1/30/2011'
    GROUP 
        BY Dt_From
         , Dt_to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    6

    still exists problem -- subquery returned more than 1 value

    Thanks for your reply -
    but i still have the problem -
    actually i don't want to count the no. of case where wg_list_prepared='y',but to count work_code if there wage list is prepared.
    Let I explain you more --

    1. Actually this database is of MGNREGA. in this project the labour work on a work site which is work_code in table.
    2. for each work wode MR is issued. Each MR contain 10 labour(rj24musteroll1011). the table rj24musterrollmaster1011 have 1 record for each MR
    3. NOW i want to determine no. of work site on which MR issued and no. of work sites whose wage list is prepared(payment to labour is done).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't understand your data and i don't understand what you want counted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    6

    Question still exists the problem

    Sir,
    Plz have a look of the database structure and then try to solve my problem -
    my table has following structure --

    MSR_NO | WORK_CODE | DT_FROM | DT_TO |WG_LIST_PREPARED
    ---------------------------------------------------------------
    2525100 |IC/3 |1/1/2011 |1/15/2011 |Y

    2525101 |IC/3 |1/1/2011 |1/15/2011 |Y

    2525102 |WC/9 |1/1/2011 |1/15/2011 |NULL

    2525103 |WC/9 |1/1/2011 |1/15/2011 |NULL

    2525104 |IC/5 |1/5/2011 |1/19/2011 |Y

    2525105 |IC/5 |1/5/2011 | 1/19/2011 |Y
    ------------------------------------------------------

    NOW THE RESULT SHOULD BE

    DT_FROM | DT_TO |NO_OF_WORKS | WORKS_WAGE_LIST_PREPARED
    ---------------------------------------------------------------
    1/1/2011 |1/15/2011 | 2 | 1

    1/5/2011 |1/19/2011 |1 | 1
    --------------------------------------------------------------
    means to say in given period how many works execute and how many works have been paid !

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those numbers don't add up properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2011
    Posts
    6

    Question now solve the problem

    Hi Rudy !
    Now have a look

    MSR_NO | WORK_CODE | DT_FROM | DT_TO |WG_LIST_PREPARED
    ---------------------------------------------------------------
    2525100 |IC/3 |1/1/2011 |1/15/2011 |Y

    2525101 |IC/3 |1/1/2011 |1/15/2011 |Y

    2525102 |WC/9 |1/1/2011 |1/15/2011 |NULL

    2525103 |WC/9 |1/1/2011 |1/15/2011 |NULL

    2525104 |IC/5 |1/5/2011 |1/19/2011 |Y

    2525105 |IC/5 |1/5/2011 | 1/19/2011 |Y
    ------------------------------------------------------

    NOW THE RESULT SHOULD BE

    DT_FROM | DT_TO |NO_OF_WORKS | WORKS_WAGE_LIST_PREPARED
    ---------------------------------------------------------------
    1/1/2011 |1/15/2011 | 2 |1

    1/5/2011 | 1/19/2011 | 1 |1
    --------------------------------------------------------------
    means to say in given period how many works execute and how many works have been paid !

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please explain in words where each of the 6 rows shows up in the counts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2011
    Posts
    6

    Question Still ???

    Hi Rudy !
    Here is the complete description of example which i have mentioned in earlier post-

    - There are 4 msr_no(2525100 TO 2525103)which start from 1/1/2011
    - In other words there are 2 distinct work_code(IC/3 & WC/9) which starts from 1/1/2011.
    - Among 2 distinct work_code(IC/3 & WC/9) IC/3 is paid(means- wg_list_prepared='Y') and WC/9 is unpaid.
    - That's why the result should be
    --------------------------------------------------------------------
    DT_FROM | DT_TO |NO_OF_WORKS | WORKS_WAGE_LIST_PREPARED
    ---------------------------------------------------------------
    1/1/2011 |1/15/2011 | 2........................|1
    ----------------------------------------------------------------------
    * Now look at the 5th & 6th row of example.
    * There are 2 msr_no (2525104 & 2525105) which starts from 1/5/2011
    * In another words there are 1 distinct work_code(IC/5) which starts from 1/5/2011
    * Among 1 distinct work_code (IC/5) it is paid(wg_list_prepared='Y')
    That's why the result should be -

    --------------------------------------------------------------------
    DT_FROM | DT_TO |NO_OF_WORKS | WORKS_WAGE_LIST_PREPARED
    ---------------------------------------------------------------
    1/5/2011 |1/19/2011 | 1........................|1
    ----------------------------------------------------------------------

    Plz provide solution this time.....

    thanks-

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the query in post #2, but replace this --
    Code:
         , COUNT(CASE WHEN wg_list_prepared = 'y'
                      THEN wg_list_prepared
                      ELSE NULL END) AS Expr1
    with this --
    Code:
         , COUNT(DISTINCT CASE WHEN wg_list_prepared = 'y'
                               THEN Work_Code
                      ELSE NULL END) AS WORKS_WAGE_LIST_PREPARED
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2011
    Posts
    6

    Question Thanks

    Hi Rudy !

    Thanks a lot for your kind support !
    The query works for me !!

    Thanks !

Tags for this Thread

Posting Permissions

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