-1

I have a table that looks like this

+-----+-----+-----+-----+
| mid | rtn | ip  | mtc |
+-----+-----+-----+-----+
| A   | 2   | ab  | acc |
| A   | 1   | ab  | qty |
| A   | 5   | ab  | rel |
| A   | 1   | cd  | rel |
| A   | 3   | cd  | qty |
| B   | 1   | ab  | acc |
| B   | 4   | ab  | rel |
| B   | 3   | ab  | qty |
| B   | 5   | cd  | acc |
| B   | 1   | cd  | rel |
+-----------------------+

I am trying to get a count for each mid for each unique mtc values so the result table looks like this:

+-----+-----------+------------+-----------+
| mid | qty_total | rel_total  | acc_total |
+-----+-----------+------------+-----------+
|  A  |     1     |      2     |    2      |
|  B  |     2     |      1     |    2      |
+------------------------------------------+

The query I have so far that I have issues with:

select mid,
   (select count(*) from t_r where mtc = 'qty' group by mid) as 'qty_total',
   (select count(*) from t_r where mtc = 'rel' group by mid) as 'rel_total',
   (select count(*) from t_r where mtc = 'acc' group by mid) as 'acc_total'
from t_r
group by mid;

When I run the query, I get

[21000][1242] Subquery returns more than 1 row

Strawberry
  • 33,750
  • 13
  • 40
  • 57
hello
  • 1,168
  • 4
  • 24
  • 59
  • If appropriate, consider handling display issues like these in application code. – Strawberry Dec 03 '18 at 15:40
  • Just curious, if the database could handle such display is there any reason to wait for application code? – hello Dec 03 '18 at 15:48
  • 2
    yes, if you want to get the data from the DB in a more generic way, e.g. to be re-used in different scenarios in the application. Also yes if doing it in the application would be more efficient. – ADyson Dec 03 '18 at 15:56

1 Answers1

1

You could do this by using a joined query

select distinct mid, [qty_total].c, rel_total.c, acc_total.c
from t_r

left join 
(select count(*) c, mid from t_r where mtc = 'qty' group by mid) as  [qty_total]
ON t_r.mid = [qty_total].mid

left join 
(select count(*) c, mid from t_r where mtc = 'rel' group by mid) as [rel_total]
ON t_r.mid = [rel_total].mid

left join 
(select count(*) c, mid from t_r where mtc = 'acc' group by mid) as [acc_total]
ON t_r.mid = [acc_total].mid
jimmy8ball
  • 746
  • 5
  • 15