October 23, 2008 at 12:49 pm
Let me start by saying I'm no SQL guru by any means, but I can create some basic queries.
Ok, here is my question
I have 2 tables that I need to get data out of in a certain way.
tbl1 looks like this
region_id region
1 North East
2 South East
3 South West
and so on
tble 2 looks like
region_id reviewed_date grade
1 10/10/2008 1.0
1 09/09/2008 2.0
1 08/08/2008 1.0
2 09/09/2008 1.0
2 10/10/2008 3.0
and so on,
I need to get the data and show it like this
Region Total Reviews LowGrades MedGrade HighGrade
North East 3 2 1
South East 2 1 1
I have a portion of my query but I'm stuck on getting the rest of the date like I need
I have:
select tbl.Region, Count(reviewed_date) as ReviewCount,
lGrade = ISNULL((Select count(tbl.grade) as LowGrade from tbl1),0)
from tbl1 INNER JOIN
tbl2 ON tbl1.Region_id = tbl2.Region_id
what am I missing or what is wrong with this, how can I get my data in the format I have listed above?
October 23, 2008 at 1:04 pm
You should provide code to populate the sample data (see Best Practice for Forum)
If you're using SQL 2005, I suggest you look into PIVOT functions (cross-tab)
This is what it's for
Otherwise, for a quick lazy fix
do the GROUP BY based on region_id
then you can do SUBQUERY for each Low/Mid/High grade
October 23, 2008 at 1:08 pm
that's kind of what I'm doing? right?
but I'm getting this as my result:
Region Total Reviews LowGrades MedGrade HighGrade
North East 3 2 1 1
South East 2 2 1 1
with this query:
select tbl.Region, Count(reviewed_date) as ReviewCount,
lGrade = ISNULL((Select count(tbl.grade) as LowGrade from tbl1),0)
from tbl1 INNER JOIN
tbl2 ON tbl1.Region_id = tbl2.Region_id
October 23, 2008 at 1:31 pm
See if this gives you a headstart
Not the ultimate best solution without knowing all details
[font="Courier New"]DECLARE @test TABLE (
region_id INT,
reviewed_date DATETIME,
grade FLOAT
)
INSERT INTO @test
SELECT
'1','10/10/2008','1.0'
UNION ALL SELECT
'1','09/09/2008','2.0'
UNION ALL SELECT
'1','08/08/2008','1.0'
UNION ALL SELECT
'2','09/09/2008','1.0'
UNION ALL SELECT
'2','10/10/2008','3.0'
SELECT *
,TotalNumGrades = COALESCE([1.0],0)+COALESCE([2.0],0)+COALESCE([3.0],0)
FROM
(
SELECT region_id
, grade, numInGrade = COUNT(1)
FROM @test
GROUP BY region_id, grade
) T PIVOT ( MIN(numInGrade) FOR Grade IN ([1.0],[2.0],[3.0]) ) P
[/font]
Results
region_id1.02.03.0TotalNumGrades
121NULL3
21NULL12
October 23, 2008 at 1:52 pm
Here's another approach w/o the PIVOT.
SET NOCOUNT ON
DECLARE @table1 TABLE(region_id int, region varchar(15))
INSERT INTO @Table1
SELECT 1, 'North East' UNION ALL
SELECT 2, 'South East' UNION ALL
SELECT 3, 'South West'
DECLARE @Table2 TABLE(region_id int, reviewed_date datetime, grade decimal(4,1))
INSERT INTO @Table2
SELECT 1, '10/10/2008', '1.0' UNION ALL
SELECT 1, '09/09/2008', '2.0' UNION ALL
SELECT 1, '08/08/2008', '1.0' UNION ALL
SELECT 2, '09/09/2008', '1.0' UNION ALL
SELECT 2, '10/10/2008', '3.0'
SELECT t1.Region,
t2.[Total Reviews],
t2.LowGrades,
t2.MedGrade,
t2.HighGrade
FROM @Table1 t1
INNER JOIN (
SELECT t1.region_id,
COUNT(reviewed_date) as 'Total Reviews',
(SELECT COUNT(*) FROM @table2 WHERE grade = '1.0' AND Region_id = t1.Region_id) as LowGrades,
(SELECT COUNT(*) FROM @table2 WHERE grade = '2.0' AND Region_id = t1.Region_id) as MedGrade,
(SELECT COUNT(*) FROM @table2 WHERE grade = '3.0' AND Region_id = t1.Region_id) as HighGrade
FROM @Table1 t1
LEFT JOIN @Table2 t2
ON t1.Region_ID = t2.Region_ID
GROUP BY t1.Region_id
) t2
ON t1.Region_ID = t2.Region_ID
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply