SQL Help

Answered

Comments

5 comments

  • Avatar
    Michael Becker

    Hi Kitty,

     

    you need to union multiple sets in order to query them as single one:

    SELECT 1st query

    UNION

    SELECT 2nd query

    UNION

    SELECT 3rd query and so on

    Each query has to have exactly teh same set of columns. Hope it helps.

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    Kitty Louie (Edited )

    Thanks for looking but I'm not trying to combine tables....I'm trying to create individual columns.  Works one I only have one column but when I try to add more the preview will error but the sql validator passes.

     

    SELECT 
    (SELECT count(events.RequestID) FROM [events_distinct] events WHERE events.status NOT IN ('error', 'success', 'success_nodata')) as incomplete,
    (SELECT count(events.RequestID) FROM [events_distinct] events WHERE events.isAsync NOT IN ('true', 'false')) as up,
    (SELECT count(events.RequestID) FROM [events_distinct] events WHERE events.isAsync = 'true' AND events.isScheduled='false') as emailed,
    (SELECT count(events.RequestID) FROM [events_distinct] events WHERE events.isAsync = 'true' AND events.isScheduled='true') as scheduled
    0
    Comment actions Permalink
  • Avatar
    Katie Garrison

    Hi Kitty,

    I think you could try something like this to create separate count columns:

    SELECT 
    sum(CASE WHEN events.status <> 'error' AND events.status <> 'success' AND events.status <> 'success_nodata' THEN 1 ELSE 0 END ) count1,
    sum(CASE WHEN events.isAsync <> 'true' AND events.isAsync <> 'false' THEN 1 ELSE 0 END ) count2,
    sum(CASE WHEN events.isAsync = 'true' AND events.isScheduled='false' THEN 1 ELSE 0 END ) count3,
    sum(CASE WHEN events.isAsync = 'true' AND events.isScheduled='true' THEN 1 ELSE 0 END ) count4

    FROM [events_distinct]

    I got something like this to work on the Sample ECommerce cube:

    SELECT 
    sum(CASE WHEN b.[Brand ID] > 200 THEN 1 ELSE 0 END ) count1,
    sum(CASE WHEN b.[Brand ID] > 10 THEN 1 ELSE 0 END ) count2

    FROM [Brand] AS b

    Hope that helps,
    Katie Garrison | Technical Solutions Consultant 

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Agreed,

    misunderstood initial idea.

    0
    Comment actions Permalink
  • Avatar
    Kitty Louie

    Works now!  Thanks Katie!  Thanks Michael!

    0
    Comment actions Permalink

Please sign in to leave a comment.