Convert bool to int in postgresql [duplicate]

Question

I am trying to run the following sql statement.

SELECT
item.item_number, SUM(item.item_active)
FROM 
public.item
GROUP BY item.item_number;

I am returning the following error:

ERROR:  function sum(boolean) does not exist

I figure if I can use a function to change the item.item_active to an integer, then it can take the sum of the active records.

Gurwinder Singh · Accepted Answer · 2017-05-11 17:58:52Z

68

Try boolean_col::int:

SELECT
item.item_number, SUM(item.item_active::int)
FROM 
public.item
GROUP BY item.item_number;

answered May 11, 2017 at 17:58

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

1 Comment

thanks, I like this solution the most because it is the simplest.

Rams · Accepted Answer · 2017-05-11 18:04:31Z

6

If that value you are getting is boolean then you can write case statement to count your active records.

SELECT
item.item_number, SUM(case when item.item_active then 1 else 0 end)
FROM 
public.item
GROUP BY item.item_number;

answered May 11, 2017 at 18:04

Rams

2,1591 gold badge15 silver badges20 bronze badges