Obtain Values for Chart

  • Hi folks,

    I'm trying to obtain the count of Experience Levels (high, low, medium & no experience) in computers. I have managed to obtain the count of high levels with the statement below but i would like if possible to obtain and include in the same statement the count of low, medium and no experience levels also but having difficulty including this in my statement. I need all the values to create a chart showing the No. and Levels in a chart.

    SELECT COUNT(dbo.LK_EXPHistory.Exp_High) AS High, dbo.EXP_Area.Area, dbo.EXP_Category.Category

    FROM dbo.LK_EXPHistory INNER JOIN

    dbo.EXP_Category ON dbo.LK_EXPHistory.EXPCategoryID = dbo.EXP_Category.EXPCategoryID INNER JOIN

    dbo.EXP_Area ON dbo.LK_EXPHistory.EXPAreaID = dbo.EXP_Area.EXPAreaID

    WHERE (dbo.EXP_Area.Area = 'Computers') AND (dbo.LK_EXPHistory.Exp_High = 'True')

    GROUP BY dbo.LK_EXPHistory.Exp_High, dbo.EXP_Area.Area, dbo.EXP_Category.Category

    HAVING (dbo.EXP_Area.Area = 'Computers')

    The other columns are LK_EXPHistory.Exp_Low, LK_EXPHistory.Exp_Medium, & LK_EXPHistory.Exp_None

    Any help would be appreciated,

    Thanks,

    Sully

  • Remove that first group by value..

    SELECTCOUNT(dbo.LK_EXPHistory.Exp_High) AS High,

    COUNT(LK_EXPHistory.Exp_Low) AS low ,

    COUNT(LK_EXPHistory.Exp_Medium) AS medium,

    COUNT(LK_EXPHistory.Exp_None) AS [No Expreience],

    dbo.EXP_Area.Area,

    dbo.EXP_Category.Category

    FROM dbo.LK_EXPHistory

    INNER JOIN dbo.EXP_Category

    ON dbo.LK_EXPHistory.EXPCategoryID = dbo.EXP_Category.EXPCategoryID

    INNER JOIN dbo.EXP_Area

    ON dbo.LK_EXPHistory.EXPAreaID = dbo.EXP_Area.EXPAreaID

    WHERE (dbo.EXP_Area.Area = 'Computers')

    AND (dbo.LK_EXPHistory.Exp_High = 'True')

    GROUP BY dbo.EXP_Area.Area, dbo.EXP_Category.Category

    HAVING (dbo.EXP_Area.Area = 'Computers')

    ---------------------------------------------------------------------------------

  • Thanks dude. That works and rocks! Going through it now to save it in my brain 🙂

  • Just tried it again but it seems to be getting caught up in himself or maybe i am going bonkers 🙂

    The columns exp_high, exp_med, exp_low, exp_none etc. are bit format (true/false) but the count seems to be not operating correctly as the statement must only pick up True values for each column, not just the exp_high column which i had incorrectly in my first statement.

    Does that make sense?

  • hmm.. bit hard to understand this way.. do you think its possible to simulate and give some test data and table scripts. Read this article on how to do that..

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    ---------------------------------------------------------------------------------

  • Since you are relatively new here (SSC), lemme help you with this...

    Is this what you need, may be you need to SUM it instead of count?

    CREATE Table #exp(High bit, low bit, medium bit, [none] bit, area varchar(50), category varchar(50))

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 0, 1, 0, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 0, 0, 1, 'SS2K5', 'database')

    INSERT INTO #exp VALUES (0, 0, 1, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 0, 0, 1, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 0, 0, 1, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 0, 0, 1, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (0, 1, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'Oracle', 'database')

    INSERT INTO #exp VALUES (1, 0, 0, 0, 'Oracle', 'database')

    SELECT SUM(CASE WHEN High = 1 THEN 1 ELSE 0 END) AS High,

    SUM(CASE WHEN LOW = 1 THEN 1 ELSE 0 END) AS low ,

    SUM(CASE WHEN Medium = 1 THEN 1 ELSE 0 END) AS

    medium,

    SUM(CASE WHEN [None] = 1 THEN 1 ELSE 0 END) AS [No

    Expreience],

    Area,

    Category

    FROM #exp

    GROUP BY Area, Category

    ---------------------------------------------------------------------------------

  • I mean, applying it to your query, it would become..

    SELECT SUM(CASE WHEN dbo.LK_EXPHistory.Exp_High = 1 THEN 1 ELSE 0 END) AS High,

    SUM(CASE WHEN LK_EXPHistory.Exp_Low= 1 THEN 1 ELSE 0 END) AS low ,

    SUM(CASE WHEN LK_EXPHistory.Exp_Medium= 1 THEN 1 ELSE 0 END) AS medium,

    SUM(CASE WHEN LK_EXPHistory.Exp_None= 1 THEN 1 ELSE 0 END) AS [No Expreience],

    dbo.EXP_Area.Area,

    dbo.EXP_Category.Category

    FROM dbo.LK_EXPHistory

    INNER JOIN dbo.EXP_Category

    ON dbo.LK_EXPHistory.EXPCategoryID = dbo.EXP_Category.EXPCategoryID

    INNER JOIN dbo.EXP_Area

    ON dbo.LK_EXPHistory.EXPAreaID = dbo.EXP_Area.EXPAreaID

    WHERE (dbo.EXP_Area.Area = 'Computers')

    AND (dbo.LK_EXPHistory.Exp_High = 'True')

    GROUP BY dbo.EXP_Area.Area, dbo.EXP_Category.Category

    HAVING (dbo.EXP_Area.Area = 'Computers')

    ---------------------------------------------------------------------------------

  • That is perfect and amazing. I would have never achieved that. I've been looking all over the net for some examples.

    Thanks again man. That's magic

  • Thanks for the feedback 🙂 and try to give test data like that in the future whenever you need help and lot of folks here would be ready to help you.

    ---------------------------------------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

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