case when with count ms sql
-
how can I achieve this result below?
| id | id_status | rate |
| --- | ----------- | ------ |
| 25 | X |62.5% |
| 15 | Y |37.5% |
having tried this
SELECT
COUNT(tab.id) AS id,
tab.status AS id_status,
(CASE
WHEN tab.status = 'X' THEN (25/40) * 100 -- this is where I'm stucked (40 = total of ids)
WHEN tab.status = 'Y' THEN 100 - ((25/40) * 100)
END AS rate
FROM table AS tab
WHERE tab.status in ('X', 'Y') -
Welcome to the forum.
You need to tell us what the source data looks like in order for us to tell you how to achieve the result.
Alternatively, be more specific with your question. It's difficult to tell exactly what the issue is at the moment.
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 -
Are you trying to get something like this? (Untested and probably has error!)
SELECT pcx = SUM(IIF(tab.status = 'X', 1, 0)) * 100.0 / COUNT(1)
,pcy = SUM(IIF(tab.status = 'Y', 1, 0)) * 100.0 / COUNT(1)
FROM tbl tab
WHERE tab.status IN ( 'X', 'Y' );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 -
Thanks !
I have a table with 'statues' column. I can have the total number of each statut(x,y,z) with count. (MS SQL)
|status| count(status) |
|x | 2 |
|y | 3 |
|z | 5 |What I'm looking for is the percentage occurrence of each status
| statut| number of statut | rate(%) |
|x | 2 | 20 |
|y | 3 | 30 |
|z | 5 | 50 |with
rate(%) x = (2/(2+3+5))*100
rate(%) y = (3/(2+3+5))*100
rate(%) z = (5/(2+3+5))*100
SELECT tab.status as status,
COUNT(tab.status) as total_nber_of_status
FROM table as tab
GROUP BY tab.status -
Here is one way. (For future reference, please note how I set up sample data in a form which can be cut & pasted directly into SSMS.)
DROP TABLE IF EXISTS #t;
CREATE TABLE #t
(
Stat CHAR(1)
,ct INT
);
INSERT #t
(
Stat
,ct
)
VALUES
('x', 2)
,('y', 3)
,('z', 5);
WITH Tot
AS (SELECT t = SUM(ct)
FROM #t)
SELECT t.Stat
,t.ct
,Rate = t.ct * 100 / Tot.t
FROM #t t
CROSS JOIN Tot;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 -
It worked. I will try to do the same with my data and let you know! Thanks
-
Honestly, I don't end up with the #t, ct etc.
I tried to replace anything regarding my data but didnt work
Sorry
- 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
Please post what you tried and I will try to help you fix it up.
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 -
WITH Temp
AS (SELECT tab = SUM(tab.status) -- total_nber_of_status
FROM Table)
SELECT tab.statut_code, -- x, y, z
tab.status,
Rate = tab.status * 100 / Temp.tab
FROM Table tab
CROSS JOIN Temp
/*
SELECT tab.status as status,
COUNT(tab.status) as total_nber_of_status
FROM table as tab
GROUP BY tab.status
*/ -
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:
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 - 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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
... ,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ()) ...
I believe you want decimal(4, 1) rather than (3, 1) (at least I can't see any reason why 100% of one group might not all be the same result/value).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:Jeff Moden wrote:
... ,PercentOfWhole = CONVERT(DECIMAL(3,1),StatusCount*100.0/SUM(StatusCount) OVER ()) ...
I believe you want decimal(4, 1) rather than (3, 1) (at least I can't see any reason why 100% of one group might not be the all same result/value).
Correct and thanks for the catch, Scott. I'm fixing it in the code now. I appreciate it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply