How to calculate database columns results and group them to achieve a manageable output

Question

I am trying to calculate in an easy way that will allow me to manage the following database table:

code ----- companyID ----- bought----- returned
abcd ----- 32        ----- 1       ----- 0
efgh ----- 32        ----- 1       ----- 1
ijkl ----- 58        ----- 1       ----- 1
mnop ----- 65        ----- 1       ----- 0

and I am trying to get the desired (below result) with this query

SELECT COUNT(*) AS bought, companyID FROM table 
WHERE bought= 1 AND refunded = 0

UNION ALL 
SELECT COUNT(*) AS returned, companyID FROM table 
WHERE returned= 1

GROUP BY companyID

which is wrong.

Here is the desired output:

companyID ----- codesTotal ----- boughtTotal ----- returnedTotal
32        ----- 2          ----- 2            ----- 1
58        ----- 1          ----- 1            ----- 1 
65        ----- 1          ----- 1            ----- 0 

The numbers of rows of a companyID must be equal to the boughtTotal.

How can I edit the code in order to achieve this?

Answer 1

Is this what you want?

select companyid,
       count(*) as num_codes,
       sum(bought) as num_bought,
       sum(returned) as num_returned
from t
group by companyid;