CODEWITHSUNDEEP
mysql
Paul Sanchez
Paul Sanchez

Reputation: 2431

MySQL Query that will group records

I have this table [Table 1]

cid | arrived | date_arrived

The [arrived field can have a value of [T] or [F], the value is [F] the date arrived field is NULL

1 records may appear only up to maximum of 2 (1 record for arrived=T and another record for arrived=F) But there are also records that may appear only once

1 | T | 2012-02-01
2 | F | [Null]
1 | F | [Null]
3 | T | 2012-03-05

I need a query that will show something like this

cid | arrived | not_arrived
1      Yes          Yes
2      No           Yes
3      Yes          No

Upvotes: 1

Views: 52

Answers (2)

Olivier Coilland
Olivier Coilland

Reputation: 3096

This works:

SELECT
    cid,
    SUM(arrived = 'T') > 0 as arrived,
    SUM(arrived = 'F') > 0 as not_arrived 
FROM [Table 1]
GROUP BY cid;

You can try it here: http://sqlfiddle.com/#!2/2b5a7/1/0

Upvotes: 2

juergen d
juergen d

Reputation: 204854

try

select cid, 
   case when find_in_set('T', group_concat(arrived)) > 0 then 'yes'  else 'no' end as arrived, 
   case when find_in_set('F', group_concat(arrived)) > 0 then 'yes'  else 'no' end as not_arrived
from table1
group by cid

Upvotes: 0

Related Questions