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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Set operation and null

Author  Topic 

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 14:32:27
Thanks to my friend dinakar, I was able to figure out how to put together the following:

Declare @top10 table (SICDESCRIPTION varchar(100),  TOTALACCOUNTS int, ACCOUNTMV money, ACCOUNTFEELY money, SICID int, rnumber int)
Insert into @top10 
Select top 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
GROUP BY SICID,SICDESCRIPTION 		
ORDER BY ACCOUNTMV DESC


Select * from @top10 
UNION ALL
Select SICDESCRIPTION= 'Other',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 0, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )
ORDER BY ACCOUNTMV DESC


THe problem I am running into is that SICDESCRIPTION= 'Other', I have records that have null value, and my results dont show all the records because of this. Was trying to play around with isnull with that set, but am not sure if or how it can be done.

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-26 : 16:18:09
maybe replacing it with isnull(SICDESCRIPTION, 'Other') ?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 16:21:12
Will that work if I want all SICDESCRIPTION's to be = other
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-26 : 16:26:06
huh??

you mean you're putting a filtering condition in your select part???

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 16:34:00
I want to set all sicdescriptions to be set to 'other' regardless of what it is, and found out that in doing that it doesnt affect nulls, and i want it to affect nulls
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-26 : 16:38:49
so why don't you just do:
Select 'Other', COUNT(* ) AS [TOTALACCOUNTS], ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 16:43:07
I can do that but it doesnt include the nulls, If i change all my nulls to lets say 99, it works fine, if i change them all back to null, it get the same count.
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 16:44:43
quote:
Originally posted by duhaas

I can do that but it doesnt include the nulls, If i change all my nulls to lets say 99, it works fine, if i change them all back to null, it get the same count.


here is the code:

Declare @top10 table (SICDESCRIPTION varchar(100),  TOTALACCOUNTS int, ACCOUNTMV money, ACCOUNTFEELY money, SICID int, rnumber int)
Insert into @top10 
Select top 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
GROUP BY SICID,SICDESCRIPTION 		
ORDER BY ACCOUNTMV DESC


Select * from @top10 
UNION ALL
Select 'Other',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 99, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )
ORDER BY ACCOUNTMV DESC


and the results:

Warning: Null value is eliminated by an aggregate or other SET operation.

(10 row(s) affected)
SICDESCRIPTION                                                                                       TOTALACCOUNTS ACCOUNTMV             ACCOUNTFEELY          SICID       rnumber
---------------------------------------------------------------------------------------------------- ------------- --------------------- --------------------- ----------- -----------
Electric, Gas, Sanitary Services                                                                     31            1757402742.28         352185.91             49          2
Membership Organizations                                                                             353           215497709.16          239081.04             86          6
Social Services                                                                                      27            146420714.57          127029.73             83          6
Wholesale Trade-Durable Goods                                                                        19            145919010.94          115623.74             50          5
Other                                                                                                46            120584847.80          263037.09             99          35
Health Services                                                                                      27            45951745.13           104819.63             80          5
Transportation Equipment                                                                             4             42572866.74           102624.42             37          2
Educational Services                                                                                 81            33120202.39           0.00                  82          2
Hotels & Other Lodging Places                                                                        1             25813917.52           57523.71              70          1
Business Services                                                                                    7             22205073.48           69843.38              73          4
Chemicals and Allied Products                                                                        7             17068356.88           62607.21              28          2

(11 row(s) affected)
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 17:08:48
I think the non-NULL rows are included. Its just warning you that it might not be included. If you put SET ANSI_NULLS ON you will not see the msg.
Check the data once again manually.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 17:10:03
And I think that because you are not using AVG or COUNT(SICDescription). Using COUNT(*) includes NULL. The "*" can make the difference.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 17:53:53
only reason i say its not including everything is because there are aaround 1300 accounts in arizona, and as you can see the total count doesnt add up to even close
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 17:56:53
Just to verify: try this and check the result.

Select * from @top10 
UNION ALL
Select 'Other',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 99, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )
--ORDER BY ACCOUNTMV DESC
UNION 
Select 'Other 2',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 99, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   SICDESCRIPTION IS NULL 
     AND (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )

ORDER BY ACCOUNTMV DESC


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 18:13:29
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 18:17:27
did you get 12 rows of data? one with description "Other 2" ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 18:18:36
honestly, thats all i got, no data returned
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 18:29:30 


Declare @top10 table (SICDESCRIPTION varchar(100),  TOTALACCOUNTS int, ACCOUNTMV money, ACCOUNTFEELY money, SICID int, rnumber int)
Insert into @top10 
Select top 10 SICDESCRIPTION,
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID,
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE    (BRANCHSTATE = 'Arizona')
         AND SICDESCRIPTION IS NOT NULL
         AND (MONTHEND = '06/01/2007')
         AND SICID <> '00'
GROUP BY SICID,SICDESCRIPTION 		
ORDER BY ACCOUNTMV DESC

Select * from @top10 
UNION ALL
Select 'Other',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 99, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )
--ORDER BY ACCOUNTMV DESC
UNION 
Select 'Other 2',
	COUNT(* ) AS [TOTALACCOUNTS],
	SUM(ACCOUNTMV) AS [ACCOUNTMV],
	SUM(ACCOUNTFEELY) AS ACCOUNTFEELY,
	SICID = 99, 
	COUNT(DISTINCT RELATIONSHIPID) AS rnumber
FROM     SNAPSRAW
WHERE   SICDESCRIPTION IS NULL 
     AND (BRANCHSTATE = 'Arizona')
         
         AND (MONTHEND = '06/01/2007')
         and
		SICID NOT IN ( Select SICID FROM @top10 )

ORDER BY ACCOUNTMV DESC




Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 18:32:45
[code]Warning: Null value is eliminated by an aggregate or other SET operation.

(10 row(s) affected)

SICDESCRIPTION TOTALACCOUNTS ACCOUNTMV ACCOUNTFEELY SICID rnumber
---------------------------------------------------------------------------------------------------- ------------- --------------------- --------------------- ----------- -----------
Electric, Gas, Sanitary Services 31 1757402742.2800 352185.9100 49 2
Membership Organizations 353 215497709.1600 239081.0400 86 6
Social Services 27 146420714.5700 127029.7300 83 6
Wholesale Trade-Durable Goods 19 145919010.9400 115623.7400 50 5
Other 46 120584847.8000 263037.0900 99 35
Health Services 27 45951745.1300 104819.6300 80 5
Transportation Equipment 4 42572866.7400 102624.4200 37 2
Educational Services 81 33120202.3900 .0000 82 2
Hotels & Other Lodging Places 1 25813917.5200 57523.7100 70 1
Business Services 7 22205073.4800 69843.3800 73 4
Chemicals and Allied Products 7 17068356.8800 62607.2100 28 2
Other 2 0 NULL NULL 99 0

(12 row(s) affected)
[/code]
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 18:34:57
here is the number of accounts that have a null sicdescription:

select count(*) from snapsraw where sicdescription is null and branchstate = 'arizona' and monthend = '06/01/2007'


            
----------- 
976

(1 row(s) affected)



and like i said, if i update all the nulls to 99, it shows up correctly, but than that impacts other reports of mine. once again your help is appreciated
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 18:41:51
[code]select count(*) from snapsraw where sicdescription is not null and branchstate = 'arizona' and monthend = '06/01/2007'[/code]

gives me 603 which is the total for all those accounts in the list
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-26 : 18:49:43
post the results of:

SELECT TOP 15 * From snapsraw where sicdescription is null and branchstate = 'arizona' and monthend = '06/01/2007'.



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2007-07-26 : 19:18:16
quote:
Originally posted by dinakar

post the results of:

SELECT TOP 15 * From snapsraw where sicdescription is null and branchstate = 'arizona' and monthend = '06/01/2007'.



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


couldnt include all results since there are accounts numbers, and account names:

bankid branchid branchstate                                        investauthid relationshipid                                     accountmv             accountfeetd          accountfeely          category                                           categorydescription                                                                                  sicid sicdescription                                     sectorid monthend                                               
------ -------- -------------------------------------------------- ------------ -------------------------------------------------- --------------------- --------------------- --------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----- -------------------------------------------------- -------- ------------------------------------------------------ 
98     SN       Arizona                                            S            REBERAK                                            98093.2600            .0000                 .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            BLACJ.K                                            21290.1100            .0000                 4450.0000             E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            BARRMMK                                            22987.4200            .0000                 18488.5900            E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            DERMMLK                                            .0000                 .0000                 .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            ZOELMMK                                            105045.0400           450.0000              17090.0000            E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SD       Arizona                                            S            BRODKIN                                            190223.6300           .0000                 .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SD       Arizona                                            S            TEMPREL                                            .0000                 1200.0000             60700.0000            E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SD       Arizona                                            S            EDWAN.K                                            156200.6600           .0000                 17787.0000            R010                                               Revocable Trust - Investment Authority                                                               NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SD       Arizona                                            S            WEINDJK                                            24632.4400            .0000                 .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            MILLG.K                                            3249.6000             .0000                 .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            REBERAK                                            985647.2600           1300.0000             1900.0000             E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            SIMPLLK                                            1460070.9900          1500.0000             467.7900              E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            COXTAYK                                            37018.0200            1200.0000             .0000                 E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            WRIGLEK                                            25.0000               8919.5400             300.0000              E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000
98     SN       Arizona                                            S            GRASEJK                                            292006.1300           31333.3400            31275.5000            E020                                               Estate (This includes Prefix 15 Post-Death Admin. Accounts)                                          NULL  NULL                                               NULL     2007-06-01 00:00:00.000

(15 row(s) affected)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site