Results 1 to 10 of 10

Thread: Help required

  1. #1
    Join Date
    Jan 2013
    Posts
    7

    Unanswered: Help required

    I have an attribute in a table. It can have 3 values. say "init", "hwp","6mth".

    Table name is DRG_T_REF

    I want to display the count of the 3 values in a single horizontal and not vertical line.

    i have started out like this, but it is not working, it is getting syntax errors. i am not even sure if it can work this way.

    Anyway here is the sql


    SELECT SUM(IFNULL(DRGR.NOMINIT,0)) NOMINIT,
    SUM(IFNULL(DRGR.NOMHWYP,0)) NOMHWYP
    FROM SFAMD500.SFAMT677_DRG_T_REF E
    LEFT OUTER JOIN (SELECT IFNULL(COUNT(X.CO_REFERRAL_TYPE),0) AS
    NOMINIT,
    SUM(IFNULL(X.CO_REFERRAL_TYPE,0)) AS NOMHWYP,
    X.CO_REFERRAL_TYPE
    FROM SFAMD500.SFAMT677_DRG_T_REF AS X
    INNER JOIN (SELECT CO_REFERRAL_TYPE
    FROM SFAMD500.SFAMT677_DRG_T_REF
    WHERE X.CO_REFERRAL_TYPE IN ('INIT','HWYP') Z
    ON (Z.TS_SYSGEN_PK = X.TS_SYSGEN_PK )
    WHERE X.CO_REFERRAL_TYPE = 'INIT'
    GROUP BY X.CO_REFERRAL_TYPE ) DRGR
    ON E.TS_SYSGEN_PK = DRGR.TS_SYSGEN_PK);
    **************************** Bottom of Data *********************

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have an attribute in a table. It can have 3 values. say "init", "hwp","6mth".

    Table name is DRG_T_REF

    I want to display the count of the 3 values in a single horizontal and not vertical line.
    Try this query
    Code:
    SELECT COUNT( CASE CO_REFERRAL_TYPE
                  WHEN 'INIT' THEN 0
                  END
                ) AS NOMINIT
         , COUNT( CASE CO_REFERRAL_TYPE
                  WHEN 'HWYP' THEN 0
                  END
                ) AS NOMHWYP
         , COUNT( CASE CO_REFERRAL_TYPE
                  WHEN '6MTH' THEN 0
                  END
                ) AS NOM6MTH
     FROM  SFAMD500.SFAMT677_DRG_T_REF
    ;

  3. #3
    Join Date
    Jan 2013
    Posts
    7

    Thanks

    Can we add a check for a valid date range say after the where condition

    where begin date > = "12/15/2012".

    I am sorry i forgot to add it in my first question

  4. #4
    Join Date
    Jan 2013
    Posts
    7

    please ignore the recent question

    please ignore the immediate above question

  5. #5
    Join Date
    Jan 2013
    Posts
    7
    i have a question, what if the count needs to satisfy two different conditions say in the above case it needs to be init as well as well as satisfy another attribute for getting the result viz result = "fail"

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    May be...
    Code:
    SELECT COUNT( CASE 
                  WHEN CO_REFERRAL_TYPE = 'INIT'
                   AND result           = 'fail' THEN
                       0
                  END
                ) AS NOMINIT
    ...
     FROM  SFAMD500.SFAMT677_DRG_T_REF
    ;

  7. #7
    Join Date
    Jan 2013
    Posts
    7

    Help required

    I have a question on the above queries. Is it possible for one count above to have a separate "where" clause. However this where clause should not affect the other counts, and it should work in the same fashion.

    If it is not possible by above query, how can we achieve that.

    Thanks in adv
    Regards

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    Hi ,tokuma , I am very glad to see you again.
    vishey68 ,do you mean this:
    Code:
    VALUES ((SELECT COUNT(* )
             FROM   SFAMD500.SFAMT677_DRG_T_REF
             WHERE  CO_REFERRAL_TYPE = 'INIT')
             ,(SELECT COUNT(* )
             FROM   SFAMD500.SFAMT677_DRG_T_REF
             WHERE  CO_REFERRAL_TYPE = 'HWYP'))

  9. #9
    Join Date
    Jan 2013
    Posts
    7

    help required

    Hello All,

    What tonkuma has given as a solution works like a charm.

    However what ineed to do is say in the case statement, after the first query i, we need something like this

  10. #10
    Join Date
    Jan 2013
    Posts
    7

    help

    SELECT COUNT( CASE CO_REFERRAL_TYPE
    WHEN 'INIT' THEN 0
    END
    ) AS NOMINIT
    COUNT( CASE CO_REFERRAL_TYPE
    WHEN 'hwyp' THEN 0
    END
    ) AS NOMINIT
    but only for the second count, in addition we need a join with other table

    and continue with the rest of count. These selects should all be in one line.

    I have given one sql in the beg, that would work if i know how to implement it. It has been done before, but i could not do that way.

Posting Permissions

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