CODEWITHSUNDEEP
mysql
cow12331
cow12331

Reputation: 245

How to combine the outcome of query?

I have a table about metro card of station enter and record record(cid, enter_sid, exit_sid) I want to get the total number of enter and out each station. For example,

cid enter_sid exit_sid 1 1 2 1 1 2 1 2 3 2 2 1

I want to get

sid   count(*)
1         3
2         4
3         1

I don't know how to combine select cid, count(*) from record group by enter_sid and select cid, count(*) from record group by exit_sid

cid means id of card. For the first row of my expected outcome, 1 is for the id of station, 3 is for sid 1 existing 2 times in enter_sid and 1 time in exit_sid.

Upvotes: 0

Views: 32

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

the trick to this is your enter and exit sid are the first column so you have to union those two together to get the correct combination... from there its a simple sum of the count.

SELECT sid, cid, SUM(counting) FROM
(
  SELECT cid, enter_sid as sid, COUNT(*) as counting FROM record GROUP BY enter_sid
  UNION ALL
  SELECT cid, exit_sid as sid, COUNT(*) as counting FROM record GROUP BY exit_sid
)t
GROUP BY sid

Working Fiddle

Upvotes: 2

Related Questions