Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: COunt how many kinds of Text

    ID | Arrived
    01 | Y
    02 | N
    03 | Y
    04 | 45
    05 | 1

    From above table I would like some result like this.
    Y: 2 N: 1 Late Minutes: 46
    I'm counting how many Y, and N. And adding up late minutes.
    If the Arrived column is numeric it's a late minutes. I'm guessing I should use GROUP BY to count.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    SELECT COUNT(*), ARRIVED
    FROM tbl
    WHERE ARRIVED IN ('Y', 'N')
    GROUP BY ARRIVED

    SELECT SUM(ARRIVED) AS 'Late Minutes'
    FROM tbl
    WHERE ARRIVED NOT IN ('Y', 'N')
    -- you might have to play around with the second query if you get an error (like insert into temp table first or create derived table)

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    gunbilegt, You can try this:
    Code:
    WITH TESTTAB (ID, ARRIVED)
      AS (SELECT '01', 'Y'  UNION ALL
          SELECT '02', 'N'  UNION ALL
          SELECT '03', 'Y'  UNION ALL
          SELECT '04', '45' UNION ALL
          SELECT '05', '1'
         )
    SELECT SUM(CASE ARRIVED
                 WHEN 'Y' THEN 1
                          ELSE 0
               END ) AS YES_CNT
         , SUM(CASE ARRIVED 
                 WHEN 'N' THEN 1
                          ELSE 0
               END ) AS NO_CNT
         , SUM(CASE ARRIVED 
                 WHEN 'Y' THEN 0
                 WHEN 'N' THEN 0
                          ELSE ARRIVED
               END ) AS LATE_MINUTES
    FROM TESTTAB

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
  •