0

I'm trying to get a ratio of 'Good' values to the Total for a given ID. The query below works when I only use one ID for the IN clause. However when I add more than one ID, the Total ends up being for all IDs and not for a given ID. Is there a way to modify the SUM to make it ID specific? I should point out that ID is NOT a Primary Key. They can repeat. These totals are number of records. They are always integers.

 SELECT ID,
   COUNT (
           CASE WHEN [Status] = 'Good'
           THEN 1
           END
         ) Good,
    SUM(COUNT(*))over() as Total
    FROM TABLE
    WHERE ID IN ('ID1','ID2')
    GROUP BY ID

If for say ID1 there are 45 records. 10 of them are Status Good. I want to return both 10 and 45 as individual values.

Improve this question
15
  • 1
    Please provide a minimal reproducible example with sample data and desired results. Also tag the RDBMS you are using. Commented Dec 2, 2024 at 22:13
  • 1
    You probably want: SUM(COUNT(*))over(partition by ID), agree with above speaker, why not provide some sample data, not many here are mindreaders and know tsql Commented Dec 2, 2024 at 22:39
  • 1
    count(*) as Total :) Commented Dec 2, 2024 at 22:47
  • 2
    @ValNik lol, that would work too! Now, i really am curious what OP wants Commented Dec 2, 2024 at 22:53
  • 1
    No it doesn't. Remove the OVER() and SUM, and just write: COUNT(*) Commented Dec 2, 2024 at 23:16

4 Answers 4

Reset to default
2

Looks like you just want:

SELECT
  ID,
  SUM(CASE WHEN Status = 'Good' THEN 1 END) AS Good,
  COUNT(*) AS Total
FROM TABLE
WHERE ID IN ('ID1','ID2')
GROUP BY ID
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try this query and let me know if this resolve your issue

Sample Data (TABLE)

    ID  Status
    ID1 Good
    ID1 Bad
    ID1 Good
    ID2 Good
    ID2 Bad
    ID2 Bad
    ID2 Good

SELECT 
    ID, 
    COUNT(CASE WHEN [Status] = 'Good' THEN 1 END) AS Good, 
    COUNT(*) AS Total, 
    SUM(COUNT(*)) OVER () AS OverallTotal
FROM TABLE 
WHERE ID IN ('ID1', 'ID2') 
GROUP BY ID;

Output:

ID  Good    Total   OverallTotal
ID1   2       3         7
ID2   2       4         7
Improve this answer

2 Comments

That's what I currently have. :) It presents the total across all IDs as opposed to total per ID. partition seems work.
I just updated my answer with some sample input and output @John
0 
SELECT
  ID,
  SUM(
    CASE 
      WHEN [Status] = 'Good' THEN 1
      ELSE 0
    END
  ) * 1.0 / COUNT(*),
FROM 
  TABLE
WHERE 
  ID IN ('ID1','ID2')
GROUP BY 
  ID
Improve this answer

1 Comment

Again that does not give the total. It gives either 1 or 0. The total should be in the 40s for the example I'm using.
0

Generally regardless of SQL standard you'd be using - window functions will always be slower than regular aggregating functions therefore, if you don't have to - it's better to avoid them, hence for you this should do the trick:

SELECT
  ID,
  SUM(
    CASE 
      WHEN [Status] = 'Good' THEN 1
      ELSE 0
    END
  ) AS good,
  COUNT(*) AS total
FROM 
  TABLE
WHERE 
  ID IN ('ID1','ID2')
GROUP BY 
  ID
Improve this answer

4 Comments

That gives either 0 or 1 every single time. Not the total per ID
@John needs a * 1.0 otherwise you get integer division
It still gives figures around 1 or 0. It should be in the 40s for the ID I'm trying. That's total regardless of status. So it can never be a decimal. Always an integer.
@John it really looked like you were after share of good. Fixed now to be separated good and total

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.