Re: using aggregates on NULL - Database Discussion Boards

Rob Philpott22-Mar-06 4:48

22-Mar-06 4:48

Hi database people,

If I have a table with one columm (col) and these rows:

A
A
B
C
C
C
NULL
NULL

and I run the query:

select col, count(col) group by col

I get:

A 2
B 1
C 3
NULL 0

How would I change it so that I got the count of nulls (ie. 2) rather than 0?

Any help appreciated!

Regards,
Rob Philpott.

Re: using aggregates on NULL

Michael Potter22-Mar-06 4:54

Michael Potter

22-Mar-06 4:54

Give NULL a value that can be counted.

SELECT
    ISNULL(col,'NULL') AS col, 
    COUNT(col) 
GROUP BY 
    col

Re: using aggregates on NULL

Rob Philpott22-Mar-06 6:14

Rob Philpott

22-Mar-06 6:14

yeah, that would do it. Thanks for the reply but if I want to see the count of null alongside the other results?

eg.

A 2
B 1
C 3
NULL 2

?

Regards,
Rob Philpott.

Re: using aggregates on NULL

Michael Potter22-Mar-06 8:29

Michael Potter

22-Mar-06 8:29

I believe it does put it alongside.

Re: using aggregates on NULL

Rob Philpott22-Mar-06 8:59

Rob Philpott

22-Mar-06 8:59

Quite right! Genious. Perhaps I should have tried it first...

Thanks for your help. Smile | :)

Regards,
Rob Philpott.

Re: using aggregates on NULL

albCode22-Mar-06 4:55

albCode

22-Mar-06 4:55

select count(col) from urtable where col=NULL

_____________________

Proud to be Albanian
_____________________

Re: using aggregates on NULL

Steve Schaneville22-Mar-06 7:15

Steve Schaneville

22-Mar-06 7:15

this will do it:

SELECT	tt.col, Count(isnull(tt.col, 'NULL'))<br />
FROM	TempTesting tt<br />
GROUP BY tt.col

~Steve
www.roundpolygons.com

Re: using aggregates on NULL

Chris Meech22-Mar-06 7:22

Chris Meech

22-Mar-06 7:22

I think you need to change

SELECT tt.col, Count(isnull(tt.col, 'NULL'))

SELECT isnull(tt.col, 'NULL'), Count(tt.col)

and the group by needs the isnull check also.

Chris Meech

I am Canadian. [heard in a local bar]

When I want privacy, I'll close the bathroom door. [Stan Shannon]

BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]

Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]

-- modified at 13:23 Wednesday 22nd March, 2006

Re: using aggregates on NULL

Jorge Novo23-Mar-06 13:09

Jorge Novo

23-Mar-06 13:09

select sum case when tt.coll is null then 1 else 0 end

There are many ways as we can see Sniff | :^)

Hear,See,Learn,Understand,Practice <hslup>
Many can be good,but only ONE can be the best keep practicing

BLOB size in oracle 8?

chilituna22-Mar-06 1:56

chilituna

22-Mar-06 1:56

Hi,

managed to forgot how to figure out blob size in oracle 8.
In oracle 9 and after it's simple:

xyz blob column;
abc table;

select length(xyz) from abc;

But in oracle 8, length doesn't accept blobs.
I have vague memory it involved "lob describe" somehow or maybe vsize???

Thanks!

Procedure

ArielR22-Mar-06 0:06

ArielR

22-Mar-06 0:06

How can I do to make a procedure that return the number of rows of any table.? ... @var int OUTPUT as....

Re: Procedure

J4amieC22-Mar-06 0:12

J4amieC

22-Mar-06 0:12

Execute the following SQL Statement using ExecuteScalar

SELECT COUNT(*) FROM MyTableName

Re: Procedure

HimaBindu Vejella22-Mar-06 0:39

HimaBindu Vejella

22-Mar-06 0:39

Run this procedure in Query Analyser

CREATE PROC Usp_RowCount AS<br />
SELECT count(*) FROM fc_Employee<br />
Go

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshres
http://himabinduvejella.blogspot.com

Re: Procedure

ArielR22-Mar-06 3:17

ArielR

22-Mar-06 3:17

Great, thank you very much....

Links for Stored Procedure

Rajkamal_dfine22-Mar-06 0:02

Rajkamal_dfine

22-Mar-06 0:02

Hello
I need some good free e-Books or any Web Site Links for learn Stored Procedure....

N.Rajakumar B.E.,
Application Developer,
www.newdreamz.tk

Re: Links for Stored Procedure

HimaBindu Vejella22-Mar-06 0:47

HimaBindu Vejella

22-Mar-06 0:47

Stored Proc is a set of precompiled code already executed at database side
It improves the performance of the applciation

Nice article for beginners
http://www.sqlservercentral.com/columnists/sburke/introductiontostoredprocedures.asp[^]
One more link
http://www.sqlservercentral.com/columnists/rmarda/nestingstoredprocedures.asp[^]

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshres
http://himabinduvejella.blogspot.com

Re: Links for Stored Procedure

HimaBindu Vejella22-Mar-06 0:56

HimaBindu Vejella

22-Mar-06 0:56

U can even have a look at this KB article
http://www.dotnetspider.com/kb/Article1131.aspx[^]
All the best

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshres
http://himabinduvejella.blogspot.com

What do you need SQL Server for?

Link260021-Mar-06 23:19

Link2600

21-Mar-06 23:19

Hi all,

I have VS 2005 Professional, which comes with SQL Server 2005 Developer Edition. I'm in a crossroad thinking whether to install it or not.

Does ASP.net application requires SQL Server installed? Not that I'm developing asp.net, but I may need to in the future. I mainly do offline apps (Win32 /w C++).

Thanks.

Re: What do you need SQL Server for?

HimaBindu Vejella22-Mar-06 0:49

HimaBindu Vejella

22-Mar-06 0:49

It's not madatory that u only need Sql server
U many use Oracle .But any one database is must to store data in the back end
Sqlserver is the preferable one

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshres
http://himabinduvejella.blogspot.com

Re: What do you need SQL Server for?

Link260022-Mar-06 2:51

Link2600

22-Mar-06 2:51

Hey,

Thanks for your reply. What I wanted to know is, do I really need to install SQL Server? I don't develop server-side apps, I mainly do client-side apps using Win32 /w C++.

Re: What do you need SQL Server for?

Michael Potter22-Mar-06 2:59

Michael Potter

22-Mar-06 2:59

SQL server is a powerful and robust data store. It is not required for development. ASP.Net apps can use any data store or none at all depending upon the application (just like Forms based apps).

Note: If you want to make a career in software development - learn to use a SQL database.

Re: What do you need SQL Server for?

Link260022-Mar-06 3:48

Link2600

22-Mar-06 3:48

The SQL Server 2005 Developer Edition I'm going to install on my workstation is only for testing purpose, correct? When actually deploying the apps on a server machine, another copy of SQL Server 2005 should be installed there? Right?

Re: What do you need SQL Server for?

Michael Potter22-Mar-06 4:10

Michael Potter

22-Mar-06 4:10

Yes.

Note: My production enviroment has the SQL Server and IIS on seperate boxes. This gives more RAM room for each application.

Re: What do you need SQL Server for?

Link260022-Mar-06 4:15

Link2600

22-Mar-06 4:15

Michael Potter wrote:
Note: My production enviroment has the SQL Server and IIS on seperate boxes. This gives more RAM room for each application.

Wait a minute, so you're saying that the SQL Server and IIS don't need to be installed on my workstation machine? They could be on a remote server computer?

Re: What do you need SQL Server for?

Michael Potter22-Mar-06 4:50

Michael Potter

22-Mar-06 4:50

Yes, SQL Server can be anywhere on the network you want. You can even expose one directly to the Internet and access it from across the globe on the public internet (not recommended).

If you intend to develop ASP.Net applications, VS runs some kind of IIS based system when you are testing on the local box. This is a requirement unless you want to make your development really difficult.

SQL, on the other hand, is no more difficult to handle remotely as it is locally. The only thing that changes is the connection string. See http://www.connectionstrings.com/[^]

-- modified at 10:55 Wednesday 22nd March, 2006

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.