Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    17

    Unanswered: Getting a count by Grouping Data

    Hi,

    I'm working on a project where I'm trying to understand some workflow volumes based on some data I have. There is data held within a field that I need to strip out, group together and then count, the field in question is called "Annotation" and field text will always start with "Annotation"..I've compiled the below, but I can't seem to pull any information back, I'm probably missing something very simple, any ideas


    WITH RESULTS AS (
    SELECT
    CASE
    WHEN ANNOTATION LIKE '%No Action%' THEN 'No Action Taken'
    WHEN ANNOTATION LIKE '%Awaiting Customer%' THEN 'Awaiting Response'
    WHEN ANNOTATION LIKE '%Response Posted%' THEN 'Reponse Posted'
    WHEN ANNOTATION LIKE '%Under Investigation%' THEN 'Post Under Investigation'
    END AS RESULTTYPE
    FROM FACEBOOK_REPORTS
    WHERE ANNOTATION LIKE '%ANNOTATION%')

    SELECT RESULTTYPE, COUNT(*)
    FROM RESULTS
    WHERE RESULTType IN ('No Action Taken', 'Awaiting Response','Reponse Posted','Post Under Investigation')
    GROUP BY RESULTTYPE;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Isn't it related to case sensitivity of text values?

    How about this?
    WHERE ANNOTATION LIKE '%Annotation%')

  3. #3
    Join Date
    Mar 2012
    Posts
    17
    Correct - New it would be something simple...

    Many thanks for clearing this up all working as expected

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another approach could be involving, for example, UPPER function:
    Code:
    where upper(annotation) like '%ANNOTATION%'
    However, for large tables, it might cause problems (i.e. slower query execution) if there was an index on the ANNOTATION column, which would become useless because of the UPPER function. You could create a function based index, though (but now we're stepping into a performance tuning area).

  5. #5
    Join Date
    Mar 2012
    Posts
    17

    Thumbs up

    Thanks Littlefoot, my 1st concern was retrieving some data which I am now able to do, I'll certainly be looking at tweaking to enhance performance so thanks for the tip

Posting Permissions

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