Why doesn''t this code work?
-
I am working with a simple table (I'll call it 'TableName') that has two columns: TranDate (Int), and Category (VarChar(30)). From this table I would like to produce results that include the following:
Count of Transaction by Category
Percent of Total Count in each Category
This code works fine and returns just what I'd expect--the Category and the count for each Category:
SELECT Category, COUNT(*) AS TranCount
FROM TableName
GROUP BY Category
ORDER BY TranCount DESC
Now, I'd like to calculate the percent of the total TranCount and put it into the results so I tried this:
SELECT x.Category, x.COUNT(*) AS TranCount, ((SELECT COUNT(*) FROM TableName x2 WHERE x.Category = x2.Category GROUP BY x2.Category) / (SELECT COUNT(*) FROM TableName) * 100) AS TranCountPct
FROM TableName x
GROUP BY x.Category
ORDER BY X.TranCount DESC
It seems like the ((SELECT...* 100) AS TranCountPct part of the code should calculate the Count for each Group, divide it by the total TranCount for TableName, multiply it by 100 to give me a percent. However, when I run the code, all I get is zeros in my results column for TranCountPct. This is true even though I can create a separate column that gives me the TotalTranCount accurately for each row and the TranCount by Category accurately for each row.
What am I missing? Probably something simple, but I just haven't been able to make the code work.
Thanks (in advance) for your help.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply