Count for Varchar - 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 for Varchar

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-03 : 14:43:33

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct

sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 14:52:55

where are you doing distinct count? I can see only applying SUM in your query and you applying a distinct over it to get distinct sum values. For distinct count you should be using COUNT(DISTINCT...)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-03 : 14:54:54

When I do the count like you suggest above it gives me the count of the rows.

When I use sum(isnull(cast([Total Count] as float),0)) it give me 338, but it should only be 147

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 14:57:29

show your data. Apply DISTNCT inside SUM is wrong and will not fetch you correct result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Surya Alekhya
Starting Member

3 Posts

Posted - 2013-07-04 : 02:06:31

try this it should work fine
count(isnull(cast(Total_Count as float),0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 02:10:02

quote:
Originally posted by Surya Alekhya

try this it should work fine
count(isnull(cast(Total_Count as float),0))

Still wrong
This will just give number of unqiue count values available in table rather than aggregate count value itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 02:10:34

quote:
Originally posted by Surya Alekhya

try this it should work fine
count(isnull(cast(Total_Count as float),0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 03:05:56

quote:
Originally posted by Surya Alekhya

try this simple procedure

--exec totc

create procedure totc as
begin
Declare @rp1 float
Declare @rp2 float
Select Distinct
@rp1=sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

select count(@rp1) as totalcount
end

did you test this at all?
it will always return 1 as variable cant store more than one value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-08 : 07:17:43

I didn't get to try it yet. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 11:23:03

quote:
Originally posted by wsilage

I didn't get to try it yet. Thanks.

let us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources