Counting occurrences of a value in multiple columns - postgres

Question

I have a table called fixtures (I have simplified for this example) and would like to populate the last two columns (*_plus_mc_per) with the percentage of times occurred for each number with a query run against the mc_* columns. It would look like this as an example

#mc = Match Corner # mc_per = Match Corner Percentage
| mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
| 1    | 4    | 3    | null | 3           | 100             |  66             |

At the point where I run my query it looks like

 #mc = Match Corner # mc_per = Match Corner Percentage
| mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
| 1    | 4    | 3    | null | 3           | null            |  null           |

So starting with the query for one_plus_mc_per I can do this

SELECT COUNT(*) FROM fixtures WHERE coalesce(mc_0,0) >= 1 AND id = 182; 
# Using coalesce for dealing with null, will return a 0 if value null

This returns

| count |
| 1     |

If I run this query on each column individually the results returned would be

| count | count | count | count | 
| 1     | 1     | 1     | 0     |

Thus enabling me to add all the column values up and divide by my match count. This makes sense (and I thank dmfay for getting me to think about his suggestion in a previous question)

My problem is I can't run this query 4 times for example as that is very ineffective. My SQL fu is not strong and was looking for a way to do this in one call to the database, enabling me to then take that percentage value and update the percentage column

Thanks

you probably can use window functions over different windows for it. build env in rextester or db-fiddle to demo — Vao Tsun
– Vao Tsun, Commented May 24, 2018 at 7:47
could you update the question with more details about the structure & contents of the fixtures table. — Haleemur Ali
– Haleemur Ali, Commented May 24, 2018 at 7:52
i don't get how one_plus_mc_per is 100 and two_plus_mc_per is 66 — Haleemur Ali
– Haleemur Ali, Commented May 24, 2018 at 8:02

Richlewis · Accepted Answer · 2018-05-24 08:39:38Z

1

Try this:

SELECT 
    SUM(CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END) count_0,
    SUM(CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END) count_1,
    SUM(CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END) count_3,
    SUM(CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END) count_4,
FROM 
    fixtures 
WHERE  id = 182;

It will return count of all the columns in single query

I am not sure though, whats the use of id = id in your query as it will always be true.

If you want count of columns *_mc for every row with > 0 condition, try this:

SELECT 
    (CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END) as count
FROM 
    fixtures 
WHERE  id = 182;

UPDATE:

Calculating one_plus_mc_per

SELECT 
    CAST((CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END)AS DECIMAL)/match_count as one_plus_mc_per
FROM 
    fixtures 
WHERE  id = 182;

edited May 24, 2018 at 8:39

Richlewis

15.4k39 gold badges138 silver badges300 bronze badges

answered May 24, 2018 at 7:55

Rahul Jain

1,3997 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Richlewis Over a year ago

Thanks @RahulJain , the id was literally meant to mean the id of the fixture, I've updated the question. That returns exactly what I was looking for, thank you so much. I guess I can take that returned value and just have another query that divides by match count or can I do that in the same query also ?

Rahul Jain Over a year ago

Happy to help. Please accept the answer if it suffice your requirement.

Richlewis Over a year ago

Will do, just waiting for sufficient time to pass :-) .... Any thoughts on my question above ?

Rahul Jain Over a year ago

I didn't got you here. How are you calculating one_plus_mc_per? example would help.

Richlewis Over a year ago

It will be the result of count divided by the column match_count

|

Gordon Linoff · Accepted Answer · 2018-05-24 11:36:13Z

0

Postgres has very nice capabilities for answering this type of question:

SELECT COUNT(*) FILTER (WHERE mc_0 >= 1) as count_0,
       COUNT(*) FILTER (WHERE mc_1 >= 1) as count_1,
       COUNT(*) FILTER (WHERE mc_3 >= 1) as count_3,
       COUNT(*) FILTER (WHERE mc_4 >= 1) as count_4,
       AVG ( (mc_0 >= 1)::int + (mc_1 >= 1)::int + (mc_3 >= 1)::int + (mc_4 >= 1)::int
           ) as one_plus_mc_per
FROM fixtures 
WHERE id = 182;

The FILTER is ANSI-standard syntax. The conversion of booleans to numbers is a very convenient construct.

answered May 24, 2018 at 11:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Richlewis Over a year ago

Thank you Gordon, I will look at this aswell, is there any difference to the above answer?

Gordon Linoff Over a year ago

@Richlewis . . . FILTER can be more efficient. The use of the boolean-->integer conversion definitely simplifies the code.

Collectives™ on Stack Overflow

Counting occurrences of a value in multiple columns - postgres

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related