case when with count ms sql
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
Thank you Phil
- Jeff Moden wrote:TheNewbie wrote:
Honestly, I don't end up with the #t, ct etc.
I tried to replace anything regarding my data but didnt work
Sorry
The #t that Phil is using is what you should have done in your post... he converted your "text" into "readily consumable data" so that other people could test. It is NOT a part of the solution... it's part of the test setup.
Please read the article at the first link in my signature line below for why this is so very important when you post a question that looks for a coded answer.
Ok Jeff I will read the article. Thanks
- Phil Parkin wrote:
Something like this. Calling your table 'table' is a really bad idea, because 'table' is a reserved word - that is why I have surrounded it with square brackets and aliased it.
Also, is it really 'statut_code' rather than 'status_code'?
WITH temp
AS (SELECT tot = SUM(t1.status) -- total_nber_of_status
FROMt1)
SELECT t2.statut_code -- x, y, z
,t2.status
,Rate = t2.status * 100 / temp.tot
FROMt2
CROSS JOIN temp;Edit: that rendered horribly! Here's how it should look:
Im going to be crazy!!!
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f95a88bddb4ab046ea4f0b5afe65e840
I've done the same thing with my code and here is what I got:
firstly this is how I get all status and their total sum
SELECT X.status as status_code -- x, y, z
count(X.status_ID) as status_count -- status_ID contains letters i.e. 'A1A00001'
FROM myTable X
GROUP BY X.statuswith this code I have the result Im looking for (first step)
|status_code|status_count|
| x |2 |
| y |3 |
| z |5 |but now when Im using your code (to have the percentage) firstly with 'SUM' I got
WITH Temp
AS (SELECT t = SUM(status_count)
FROM my_table)
SELECT t.status
,t.status_count
,Rate = t.status_count * 100 / Temp.t
FROM my_table t
CROSS JOIN Temp;'Operand data type varchar is invalid for sum operator.'
and with COUNT I got
'Conversion failed when converting the varchar value 'A1A00001' to data type int.'
Any help
- Jeff Moden wrote:
Here's another way to post "Readily Consumable Date" followed by a different solution that uses the SUM() Windowing Function.
Here's the test data construction...
--===== Create the test table and populate it on-the-fly.
-- This is NOT a part of the solution.
-- We''re just building test data for the issue here.
DROP TABLE IF EXISTS #TestTable;
GO
SELECT v.*
INTO #TestTable
FROM (VALUES
('x',2)
,('y',3)
,('z',5)
)v([Status],StatusCount)
;
GOHere's the solution that uses the SUM() Windowing Function...
--===== The SUM() Windowing Function does a sum with the need for a GROUP BY, in this case.
-- Multiplying the divisor by 100.0 not only converts the answer to a percent but it
-- also converts the problem to a FLOAT to overcome the INTEGER MATH so that we can
-- get a more finite answer with decimal places.
-- The CONVERT(DECIMAL(4,1) is for formatting and could be skipped unless the boss
-- wants it that way. :D
SELECT [Status]
,StatusCount
,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ())
FROM #TestTable
ORDER BY [Status] --The only way to guarantee an order is to state it in the code.
;Here's the result...
Hi Jeff,
I tested your code
SELECT X.status as status_code -- x, y, z
rate = CONVERT(DECIMAL(4,1), status_count *100.0 / SUM(status_count ) OVER ())-- status_ID contains ----letters i.e. 'A1A00001'
--count(X.status_ID) as status_count
FROM myTable X
GROUP BY X.status, status_count
--ORDER BY X.statusI have this error
'Operand data type varchar is invalid for sum operator.'
-
What is the datatype of the column 'status_count'?
We have assumed that it is an integer, but I suspect that it is not.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
What is the datatype of the column 'status_count'?
We have assumed that it is an integer, but I suspect that it is not.
varchar cause there are letter in it 'A1A00001'
status_count as you already know is given/calculated throught status_ID (which are varchar)
- TheNewbie wrote:Phil Parkin wrote:
What is the datatype of the column 'status_count'?
We have assumed that it is an integer, but I suspect that it is not.
varchar cause there are letter in it 'A1A00001'
status_count as you already know is given/calculated throught status_ID (which are varchar)
How can a count contain non-numeric characters?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:TheNewbie wrote:Phil Parkin wrote:
What is the datatype of the column 'status_count'?
We have assumed that it is an integer, but I suspect that it is not.
varchar cause there are letter in it 'A1A00001'
status_count as you already know is given/calculated throught status_ID (which are varchar)
How can a count contain non-numeric characters?
I think I have to be concentrated 🙁
I dont need to have status_count using the status_ID (which are varchar)
If you look at the code right now you will notice how simple I should have done it but till for the percentage it doesnt work but I think Im not far away from the solution 🙂
|status_code|
| x |
| x |
| y |
| y |
| y |
| z |
| z |
| z |
| z |
| z |
SELECT X.status as status_code -- x, y, z
count(X.status) as status_count
FROM myTable X
GROUP BY X.status
|status_code|status_count|
| x |2 |
| y |3 |
| z |5 |
SELECT X.status as status_code -- x, y, z
rate = CONVERT(DECIMAL(4,1), status_code *100.0 / SUM(status_code ) OVER ())
--count(X.status_ID) as status_count
FROM myTable X
GROUP BY X.status
--ORDER BY X.status
|status_code|status_count| rate |
| x |2 |20.0 |
| y |3 |30.0 |
| z |5 |50.0 | -
when I try your hardcoded logic I get 0 for X and 100 for y
select 25/40 *100
select 100 - ((25/40)*100)Based on your logic same result 0 for X and 100 for y
Create TABLE #temp
(ID int
, ID_Status varchar(5)
)
Insert into #temp values (25,'x')
Insert into #temp values (15,'y')
select * from #temp
select (ID), ID_Status
,Rate = case
when ID_Status= 'x' then cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
when ID_Status= 'y' then 100 - cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
end
from #temp - Madhu g wrote:
when I try your hardcoded logic I get 0 for X and 100 for y
select 25/40 *100
select 100 - ((25/40)*100)Based on your logic same result 0 for X and 100 for y
Create TABLE #temp
(ID int
, ID_Status varchar(5)
)
Insert into #temp values (25,'x')
Insert into #temp values (15,'y')
select * from #temp
select (ID), ID_Status
,Rate = case
when ID_Status= 'x' then cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
when ID_Status= 'y' then 100 - cast(((ID/(SELECT SUM(ID)FROM #temp))*100) as decimal(16,2))
end
from #tempHi Madhu,
I think the logic looks more like this of what I want to achieve . Its like to calculate an average
2,3,5 avg (2+3+5)/3= 3.33
BUT the percentage for each status will be
(2/3)*100 for x
(3/3)*100 for y
(5/3)*100 for z
SELECT X.status as status_code, -- x, y, z
COUNT(X.status) as status_count ,
(CASE
WHEN X.status = 'x' THEN (COUNT(x)/SUM((X.status))*100
ELSE
WHEN X.status = 'y' THEN (COUNT(y)/SUM((X.status))*100
ELSE (COUNT(z)/SUM((X.status))*100 END ) AS rate
FROM myTable X
GROUP BY X.status
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply