Mysql trivial select query

Question

I have next mysql table:

id | object_id | status

where object_id is not unique and status is tinyint(-1 or 1).

That is, for one object I can have several identical and / or different statuses.

I want to make SELECT query with getting object_id, COUNT of positive status (as pos) and COUNT of negative status(as neg) WHERE neg>pos;

I tried this, but i have problem with select several(vote_up and vote_down) columns from one(vote) using condition

SELECT * FROM (
    SELECT object_id, COUNT(status) as (IF(status>0,'status_up','status_down'))
    FROM object_statuses GROUP BY object_id;
) WHERE status_up<status_down;

See: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Oct 30, 2017 at 13:26
Based on what you want to count votes? User_id or Object_id? Please add some sample data as well. — Harshil Doshi
– Harshil Doshi, Commented Oct 30, 2017 at 13:28

Fedor M. · Accepted Answer · 2017-10-30 15:06:06Z

4

By viewing your query i guess you are trying to count the negative votes and positive votes from users and return users who have greater negative votes count. If that is the case you can use below query

SELECT object_id,
sum(status  = 1) status_up,
sum(status  = -1) status_down
FROM object_statuses
GROUP BY object_id
HAVING status_up < status_down

You cannot use WHERE clause to perform filter on aggregate results instead use HAVING clause for such operations

edited Oct 30, 2017 at 15:06

Fedor M.

254 bronze badges

answered Oct 30, 2017 at 13:30

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

3 Comments

Strawberry Over a year ago

Well, you can in the context provided by the OP.

Fedor M. Over a year ago

it's good and right answer. It's my fault - I'm using two different schemas(at table and query) in my question

M Khalid Junaid Over a year ago

@Strawberry yup in parent query this can be performed but not at same level query

Collectives™ on Stack Overflow

Mysql trivial select query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related