January 19, 2010 at 7:11 am
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
January 19, 2010 at 7:23 am
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')
---------------------------------------------------------------------------------
January 19, 2010 at 7:39 am
Thanks dude. That works and rocks! Going through it now to save it in my brain 🙂
January 19, 2010 at 7:57 am
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?
January 19, 2010 at 8:34 am
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/
---------------------------------------------------------------------------------
January 19, 2010 at 8:45 am
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
---------------------------------------------------------------------------------
January 19, 2010 at 8:49 am
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')
---------------------------------------------------------------------------------
January 19, 2010 at 9:26 am
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
January 19, 2010 at 10:19 am
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