Data Population - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Population

Author

Topic

mailtosaja
Starting Member

28 Posts

Posted - 2008-10-15 : 10:17:27

Hi,
i have two tables it's called ErrorDetails and LevelDetails..Below having the table structure with data

ErrorDetails
S_No ErrorID Descri LevelID
---------------------------------------
1 1 Coding Error 1
2 2 Testing Error NULL
3 3 Review 2
4 1 Coding Error 3
5 1 Test Error 0
6 2 Test 0

LevelDetails
LevelID LevelDescription
----------------------------
0 None
1 Functional!
2 Release Level

I want the data like below structure... In the ErrorDetails
table Null and 0 are the same in LevelID...

LevelDescription count
None 2
NotMapped 1 (this is for Null value)
Functional! 1
If any body knows the query please tell me

Thanks & Regards,
S.Jess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:01:16

[code]SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(S_No) AS Count
FROM LevelDetails ld
FULL JOIN ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')[/code]

mailtosaja
Starting Member

28 Posts

Posted - 2008-10-15 : 11:13:46

Hi,

Thanks for your reply. NotMapped is not there in the Table.I would like to display 'Not Mapped' instance of NULL

Thanks & Regards,
S.Sajan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:16:22

quote:
Originally posted by mailtosaja

Hi,

Thanks for your reply. NotMapped is not there in the Table.I would like to display 'Not Mapped' instance of NULL

Thanks & Regards,
S.Sajan.

which table? i cant see in sample data posted.

mailtosaja
Starting Member

28 Posts

Posted - 2008-10-15 : 11:26:40

Hi,
In table "LevelDetails" there is no id like NULL(like 0,1,ect id in LevelDetails).But Null Entry is there "ErrorDetails".So,i would like to display Null count.
Level Description | count
None | 2
NotMapped | 1 (this is for Null value)
Functional!| 1

i think you understand my problem.

Thanks & Regards,
S.Sajan.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:27:59

Visakh16's query works, but giving 2 counts for Nulls.

FIXED:

quote:

Originally posted by visakh16

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(ed.levelid) AS Count
FROM LevelDetails ld
FULL JOIN ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:30:25

[code]LevelDescription Count
-------------------- -----------
Functional! 1
None 2
NotMapped 1
Release Level 1[/code]

quote:
Originally posted by mailtosaja

Hi,
In table "LevelDetails" there is no id like NULL(like 0,1,ect id in LevelDetails).But Null Entry is there "ErrorDetails".So,i would like to display Null count.
Level Description | count
None | 2
NotMapped | 1 (this is for Null value)
Functional!| 1

i think you understand my problem.

Thanks & Regards,
S.Sajan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:32:04

wat about this?

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(*) AS Count
FROM LevelDetails ld
FULL JOIN ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

mailtosaja
Starting Member

28 Posts

Posted - 2008-10-15 : 11:33:43

Hi hanbingl,
Did you have the entry for NotMapped in "LevelDetails" table.if it's there means it's worng.It's don't have the entry.
Thanks & regards,
S.Sajan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:36:03

quote:
Originally posted by mailtosaja

Hi hanbingl,
Did you have the entry for NotMapped in "LevelDetails" table.if it's there means it's worng.It's don't have the entry.
Thanks & regards,
S.Sajan.

both our queries dont need value NotMapped to be in table. What we have done is converting the NULL values that came to LevelDescription fields (that whose levelID values dont exists in LevelDetails table) to NotMapped, which is exactly what you want as per your sample output.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:36:31

No entry, here's the script:


declare @ErrorDetails table (
S_No int , ErrorID int, Descri char(500), LevelID int)

insert into @ErrorDetails
select 1, 1, 'Coding Error', 1
UNION ALL
select 2, 2, 'Testing Error', NULL
UNION ALL
select 3, 3, 'Review', 2
UNION ALL
select 4, 1, 'Coding Error', 3
UNION ALL
select 5, 1, 'Test Error', 0
UNION ALL
select 6, 2, 'Test', 0


declare @LevelDetails table (
LevelID int, LevelDescription char(500))
----------------------------
insert into @LevelDetails
select 0, 'None'
UNION ALL
select 1, 'Functional!'
UNION ALL
select 2, 'Release Level'

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(S_No) AS Count
FROM @LevelDetails ld
FULL JOIN @ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

select * from @leveldetails

select * from @errordetails

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(ed.levelid) AS Count
FROM @LevelDetails ld
FULL JOIN @ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:39:17

[code]
(6 row(s) affected)

(3 row(s) affected)

LevelDescription Count
-------------------- -----------
Functional! 1
None 2
NotMapped 2
Release Level 1

(4 row(s) affected)

LevelID LevelDescription
----------- --------------------
0 None
1 Functional!
2 Release Level

(3 row(s) affected)

S_No ErrorID Descri LevelID
----------- ----------- -------------------- -----------
1 1 Coding Error 1
2 2 Testing Error NULL
3 3 Review 2
4 1 Coding Error 3
5 1 Test Error 0
6 2 Test 0

(6 row(s) affected)

LevelDescription Count
-------------------- -----------
Functional! 1
None 2
NotMapped 1
Release Level 1

(4 row(s) affected)

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:39:29

quote:

Originally posted by hanbingl

No entry, here's the script:


declare @ErrorDetails table (
S_No int , ErrorID int, Descri char(500), LevelID int)

insert into @ErrorDetails
select 1, 1, 'Coding Error', 1
UNION ALL
select 2, 2, 'Testing Error', NULL
UNION ALL
select 3, 3, 'Review', 2
UNION ALL
select 4, 1, 'Coding Error', 3
UNION ALL
select 5, 1, 'Test Error', 0
UNION ALL
select 6, 2, 'Test', 0


declare @LevelDetails table (
LevelID int, LevelDescription char(500))
----------------------------
insert into @LevelDetails
select 0, 'None'
UNION ALL
select 1, 'Functional!'
UNION ALL
select 2, 'Release Level'

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(S_No) AS Count
FROM @LevelDetails ld
FULL JOIN @ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

select * from @leveldetails

select * from @errordetails

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(ed.levelid) AS Count
FROM @LevelDetails ld
FULL JOIN @ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

hanbingl, try the below also

SELECT COALESCE(ld.LevelDescription,'NotMapped') AS LevelDescription,COUNT(*) AS Count
FROM LevelDetails ld
FULL JOIN ErrorDetails ed
ON ed.LevelID=ld.LevelID
GROUP BY COALESCE(ld.LevelDescription,'NotMapped')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:42:44

still giving 2 counts for "NotMapped"... I think you've also counted LevelID 3 as NotMapped.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:53:19

quote:
Originally posted by hanbingl

still giving 2 counts for "NotMapped"... I think you've also counted LevelID 3 as NotMapped.

oh..ok...yup that will also be included.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources