SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count for Varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
53 Posts

Posted - 07/03/2013 :  14:43:33  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/03/2013 :  14:52:55  Show Profile  Reply with Quote
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
Go to Top of Page

wsilage
Yak Posting Veteran

USA
53 Posts

Posted - 07/03/2013 :  14:54:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/03/2013 :  14:57:29  Show Profile  Reply with Quote
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
Go to Top of Page

Surya Alekhya
Starting Member

3 Posts

Posted - 07/04/2013 :  02:06:31  Show Profile  Reply with Quote
try this it should work fine
count(isnull(cast(Total_Count as float),0))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/04/2013 :  02:10:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/04/2013 :  02:10:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/04/2013 :  03:05:56  Show Profile  Reply with Quote
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
Go to Top of Page

wsilage
Yak Posting Veteran

USA
53 Posts

Posted - 07/08/2013 :  07:17:43  Show Profile  Reply with Quote
I didn't get to try it yet. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/08/2013 :  11:23:03  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000