Why doesn''t this code work?
-
You could maybe put this in a procedure (definitely easier) & get the count(*) first...
Declare @TranCount Decimal(10,2)
Select @TranCount = count(*) from TableName
Select (count(*) * 100)/(@TranCount ) As Percentage from TableName
Group By Category
**ASCII stupid question, get a stupid ANSI !!!** -
You may also want to try...(I can't test this...)
SELECT x.Category, x.COUNT(*) AS TranCount, ((SELECT COUNT(*) FROM TableName x2 WHERE x.Category = x2.Category GROUP BY x2.Category) * 100/ (SELECT COUNT(*) FROM TableName) AS TranCountPct
FROM TableName x
GROUP BY x.Category
ORDER BY X.TranCount DESC
**ASCII stupid question, get a stupid ANSI !!!** -
I think I found the problem. TranCount is an Integer data type. Therefore, the division always produced a zero result because the value was a fraction less than zero, but presented as an integer. By CASTing the TranCount values as DECIMALs before doing the math, I got a proper result.
Thanks for your help.
By the way, putting part of the math into a function was a great idea, too.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Isn't this neater?
Select Category, TranCount, (TranCount * 100.0 / Total) AS TranCountPct
FROM
(SELECT Category, COUNT(*) AS TranCount
FROM TableName x
GROUP BY Category ) C
cross join
(SELECT COUNT(*) Total FROM TableName) T
* Noel -
Much neater. Should be faster too
. -
I Couldn't test it but I would think so
* Noel -
I don't need to test it to know it's gonna run faster.
-
Ah - the cross join - one that almost every single book just skims over because they can never find a use for it (except in "high level mathematical functions that make use of Cartesian products"...or use it to get a quick test database up and running)...
How very brilliant Noel - Remi, now you can enjoy your vacation in peace for you know that Noel's here to hold the fort up! By the time you come back Noel may even have made you "dispensable"....
<:-)
**ASCII stupid question, get a stupid ANSI !!!** -
I'm already dispensable. Nothing new here.
-
Awww Remi! I was only kidding - you know that scc.com would shut down if you went away!!!
**ASCII stupid question, get a stupid ANSI !!!** -
Not likely, I'm only one of many good t-sqler here. I just happen to post more than the average.
-
"Not likely, I'm only one of many good t-sqler here. I just happen to post more than the average"
That "more" should have been written in red bold italics an underlined letters
* Noel -
Yes - splly. since Remi posts at least one "hth" for every solution he provides...
Whenever I have some spare time I gather statistics on him and once I have them compiled I'm going to post them on scc.com.....
<;-) hth!
**ASCII stupid question, get a stupid ANSI !!!** -
Approximate post count since I joined :
month year posts posts/day May 2004 3 0.10 June 2004 4 0.13 July 2004 7 0.23 August 2004 17 0.55 Septembre 2004 20 0.67 Octobre 2004 12 0.39 Novembre 2004 62 2.07 December 2004 67 2.16 January 2005 114 3.68 February 2005 93 3.32 March 2005 254 8.19 April 2005 346 11.53 May 2005 417 13.45 June 2005 1538 51.27 incomplete so here it goes : MORE
-
okay - looks like he's compiling one himself - maybe we'll just do a cross join and credit him with even more posts....<:-)
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply