count - sql - 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

count - sql

Author

Topic

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 09:32:52

I have this table:

CREATE TABLE R (A INT NOT NULL, B INT NOT NULL, PRIMARY KEY (A));

and I must find which of the following statements will give me the number of records in table R.
I have these options:
(?) SELECT COUNT(DISTINCT A) FROM R;
(?) SELECT COUNT(A) FROM R;
(G) SELECT COUNT(B) FROM R;
(?) SELECT COUNT(*) FROM R;

I have run it and if I made the table correct, both 4 answers are correct. But I can't understand why. Can you explain that to me pls?
Why isn't there a difference?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:38:19

1.SELECT COUNT(DISTINCT A) FROM R
gives the count of distinct values in column A
2.SELECT COUNT(A) FROM R;
gives count of total values in column A (including duplicates except NULL values)
3.SELECT COUNT(B) FROM R;
gives count of total values in column B (including duplicates except NULL values)
4.SELECT COUNT(*) FROM R
gives number of total records in table R (includes NULL valued records also)

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 09:40:46

quote:
Originally posted by visakh16

1.SELECT COUNT(DISTINCT A) FROM R
gives the count of distinct values in column A
2.SELECT COUNT(A) FROM R;
gives count of total values in column A (including duplicates except NULL values)
3.SELECT COUNT(B) FROM R;
gives count of total values in column B (including duplicates except NULL values)
4.SELECT COUNT(*) FROM R
gives number of total records in table R (includes NULL valued records also)

So not all of them are correct. Only 4 is correct. Right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 09:42:36

No, all 4 are correct because the B column is defined as NOT NULL (it is not allowed to have a NULL value for B).
The same goes for Column A. And A column is also PRIMARY KEY, which means duplicates are not allowed.

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 09:43:26

quote:
Originally posted by misty

I have this table:

CREATE TABLE R (A INT NOT NULL, B INT NOT NULL, PRIMARY KEY (A));

and I must find which of the following statements will give me the number of records in table R.
I have these options:
(?) SELECT COUNT(~~DISTINCT~~ A) FROM R;

Since A is already PK

(?) SELECT COUNT(A) FROM R;
(G) SELECT COUNT(B) FROM R;
(?) SELECT COUNT(*) FROM R;

I have run it and if I made the table correct, both 4 answers are correct. But I can't understand why. Can you explain that to me pls?
Why isn't there a difference?

Thanks in advance.

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 09:45:47

quote:
Originally posted by Peso

No, all 4 are correct because the B column is defined as NOT NULL (it is not allowed to have a NULL value for B).
The same goes for Column A. And A column is also PRIMARY KEY, which means duplicates are not allowed.

E 12°55'05.63"
N 56°04'39.26"

And the first ? (distinct A)? Because you cannot have duplicates so there' nothing to destinct? Correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:47:53

yup...distinct is redundant there

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 09:50:00

quote:
Originally posted by visakh16

yup...distinct is redundant there

For my second day with sql,I must say I'm in love with it.

(As long I have all of you to help me).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:52:53

no worries...you're welcome

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-06 : 09:54:23

Supplemental question:

Is there any advantage (speed / resources wise) to doing the count on the primary key (PK' are implemented as clustered index(s) correct?) rather than doing it on one of the other fields (or on COUNT(*))

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 10:16:36

Primary key can be NON-CLUSTERED as well. Both will be same as they do index scan for large tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources