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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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' );If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- 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) A socialist is someone who will give you the shirt off *someone else's* back.
- 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