CODEWITHSUNDEEP
mysqlselectjoincountgroup-by
Get Off My Lawn
Get Off My Lawn

Reputation: 36341

Select Count From 1 Column Into 2 Columns

I have a table that has a column called type it holds one of two words upvote or downvote I want to group by a.question_id then get 2 columns with a count of each one.

select a.*, u.* from answers a
left join users u using(user_id)
left join vote_history v using(answer_id)
where a.question_id = 4 and deleted < 4
group by v.question_id
order by votes desc

Example opt

ColumnA  | ColumnB  | upvotes | downvotes
---------+----------+---------+----------
record1A | record2B | 3       | 2
record2A | record2B | 2       | 5

Is it possible for me to do this without doing more than one query, and without doing a sub query?

Upvotes: 1

Views: 131

Answers (2)

Taryn
Taryn

Reputation: 247820

This is known as a pivot. In MySQL you can use an aggregate function with a CASE expression to transform the data into columns:

select a.*, u.*,
    SUM(case when v.type='upvote' then 1 else 0 end) Upvotes,
    SUM(case when v.type='downvote' then 1 else 0 end) Downvotes,
from answers a
left join users u using(user_id)
left join vote_history v using(answer_id)
where a.question_id = 4 and deleted < 4
group by v.question_id
order by votes desc

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60518

This aught to work:

select 
  a.*, 
  u.*,
  SUM(IF(v.type = 'upvote',1,0) as upvotes,
  SUM(IF(v.type = 'downvote',1,0) as downvotes,
from answers a
left join users u using(user_id)
left join vote_history v using(answer_id)
where a.question_id = 4 and deleted < 4
group by v.question_id
order by votes desc

Upvotes: 1

Related Questions