Extracting multiple fields from one column

  •  

     

    Hello,

    I have a situation.

    I am trying to show each  distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event

    occurred– for labels containing “GNAV”.

    I am not sure why but something is wrong. Do you have any ideas what potentially can be wrong?

    SELECT DISTINCT

    SUBSTRING(HITS,

    CHARINDEX(''eventCategory':' ',HITS)+1,

    CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1) as eventCategory,

    COUNT(SUBSTRING(HITS,

    CHARINDEX(''eventAction':' ',HITS)+1,

    CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1)) as eventAction,

    COUNT(SUBSTRING(HITS,

    CHARINDEX(''eventLabel':' ',HITS)+1,

    CHARINDEX(' ,' ',message)-CHARINDEX(' ', ',HITS)-1)) as eventLabel,

    substring('date', 3, 2) AS 'Month'

    FROM TABLE

    WHERE HITS LIKE '%GNAV%'

    GROUP BY date

    Question1

    • This topic was modified 9 months ago by  JeremyU.
    • This topic was modified 9 months ago by  JeremyU.
  • Can you provide DDL & sample data insert script?

    You said you want counts, but you're using DISTINCT & you're not returning COUNT()

  • I cannot give DDL and sample data. Only that small part pretty much that I gave in the first message and yes, you are correct, I need to write COUNT before each extraction of the value. I believe I have corrected.

  • You generally don't need/want to use DISTINCT when you're using an aggregate like COUNT() -- you instead want to GROUP BY the SELECT columns that are not aggregated.

    Does that work? If not, can your be more specific about the error/problem than "something is wrong"?

     

    Based on the image, the data appears to almost be json:  This would work:

    {"eventInfo":{"eventCategory": "GNAV-Furniture-Living_Room","eventAction": "click", "eventLabel": "GNAV : Sofas & Sectionals"}}

    If valid json, a json-oriented query might be much simpler than the string parsing -- e.g.,

    DROP TABLE IF EXISTS #events;

    CREATE TABLE #events
    (
    VisitID INT NOT NULL PRIMARY KEY,
    Hits NVARCHAR(max) NOT NULL,
    [date] DATE NOT NULL
    );

    INSERT INTO #events
    VALUES (15692456,'{"eventInfo":{"eventCategory": "GNAV-Furniture-Living_Room","eventAction" : "click", "eventLabel" : "GNAV : Sofas & Sectionals"}}','20220201')
    -- or SELECT * FROM https://www.sqlservercentral.com/wp-content/uploads/hm_bbpui/4198354/zuiqcst2islse2b21pqavdeiz2zpgyqp.jpg if you want to try selecting from a picture instead of data :-)

    SELECT
    JSON_VALUE(Hits,'$.eventInfo.eventCategory') AS EventCategory,
    JSON_VALUE(Hits,'$.eventInfo.eventAction') AS EventAction,
    JSON_VALUE(Hits,'$.eventInfo.eventLabel') AS EventLabel
    FROM #events;
  • JeremyU wrote:

    I cannot give DDL and sample data. Only that small part pretty much that I gave in the first message and yes, you are correct, I need to write COUNT before each extraction of the value. I believe I have corrected.

    At least stop posting data as graphics.  And you could also turn that bit into some "Readily Consumable" data.  It's been explained to you many times, Jeremy.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply