Results 1 to 5 of 5

Thread: Sub query

  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Sub query

    Hi,
    Is there any way to do something like below query,
    select count(t.RECORD_ID),
    (select count(s.PUBLISHED_YN) as pub
    from TRAINING_PERSONNELS s
    where s.TRAINING_ID = t.TRAINING_ID
    and s.PUBLISHED_YN =1 and s.assignment_num is null)
    from TRAINING_PERSONNELS t
    where t.TRAINING_ID = 29055 and t.assignment_num is null

    From the same table i want to get total number of records,how many trainings are published and how many are completed ? I want to get it from the single query.
    Please help me

    Thank you,
    Kavitha

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Won't SUM(s.published_yn) do the trick? Looks like you have 1s for 'Y' in that column, so I guess 0s mean 'N'.

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    ya your suggestion works for getting total number and published but how do i get, how many are completed/in progress/not started in db this column is a varchar.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    What about
    Code:
    SUM( case when <condition for determination whether "trainings is published/completed"> then 1 end )
    ?

    Of course it would be easier to suggest something more precise, if you posted complete test case - CREATE TABLE statement for table definition, INSERT statements (up to 10) for representative sample data, required result set and description in English what are rules for achieving it.

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    Thank you,Its working fine.

Posting Permissions

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