0

I am attempting to join two tables and also get a SUM and COUNT. I need to get the SUM QTY from history table and COUNT SN for each PN and LOC from rota table.

History table:

create table history (
        code int(10) primary key,
    PN varchar(10) not null,
        LOC varchar(10) not null,
    Qty int(10) not null);

insert into history values (1,  'T1',   'AAA',  1);
insert into history values (2,  'A1',   'BBB',  2);
insert into history values (3,  'J1',   'CCC',  3);
insert into history values (4,  'A2',   'AAA',  1);
insert into history values (5,  'J2',   'BBB',  2);
insert into history values (6,  'A3',   'CCC',  3);
insert into history values (7,  'J3',   'AAA',  4);
insert into history values (8,  'T1',   'BBB',  5);
insert into history values (9,  'A1',   'CCC',  1);
insert into history values (10, 'J2',   'AAA',  3);
insert into history values (11, 'J2',   'BBB',  4);
insert into history values (12, 'A1',   'CCC',  3);
insert into history values (13, 'J2',   'AAA',  5);

Rota table

create table rota (
        code int(10) primary key,
    PN varchar(10) not null,
    SN varchar(10) not null,
        LOC varchar(10) not null);

insert into rota values (1, 'T1',   't1a', 'AAA');
insert into rota values (2, 'A1',   'a1a',  'BBB');
insert into rota values (3, 'J1',   'j1a',  'CCC');
insert into rota values (4, 'A2',   'a2a',  'AAA');
insert into rota values (5, 'J2',   'j2a',  'BBB');
insert into rota values (6, 'A3',   'a3a',  'CCC');
insert into rota values (7, 'J3',   'j3a',  'AAA');
insert into rota values (8, 'T1',   't1b',  'BBB');
insert into rota values (9, 'A1',   'a1b',  'CCC');
insert into rota values (10,    'J2',   'j2b',  'AAA');
insert into rota values (11,    'J2',   'j2c',  'BBB');
insert into rota values (12,    'A1',   'a1c',  'CCC');
insert into rota values (13,    'J2',   'j2d',  'AAA');
insert into rota values (14,    'J2',   'j2e',  'AAA');
insert into rota values (15,    'J2',   'j2f',  'AAA');

The desired result is the following table

 PN      LOC     SUM(QTY)    COUNT(SN) 
 A1      BBB         2          1
 A1      CCC         4          2
 A2      AAA         1          1
 A3      CCC         3          1
 J1      CCC         3          1
 J2      AAA         8          4
 J2      BBB         6          2
 J3      AAA         4          1
 T1      AAA         1          1
 T1      BBB         5          1
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Kalenji
  • 401
  • 2
  • 19
  • 42

1 Answers1

1

Use sub-queries like so:

select a.pn, a.loc, a.q, b.c from 
  (select h.pn, h.loc, sum(qty) q from history h group by h.pn, h.loc) a
  join
  (select r.pn, r.loc, count(sn) c from rota r group by r.pn, r.loc) b
  on a.pn = b.pn and a.loc = b.loc
  order by a.pn;

(tried it on Oracle, don't have MySQL available right now, but it should work fine or with minor adaptations)

memo
  • 1,868
  • 11
  • 19
  • It works. If I want to add another table this time with location and restrict only to 'AAA' and 'BBB', how can I do it? – Kalenji Jul 24 '18 at 14:35
  • @Kalenji Add the new table to the join (or make another sub-query if you need special grouping). Add WHERE loc IN ('AAA', 'BBB') after the join. – memo Jul 24 '18 at 14:40
  • Thanks, all works now. I will post the following question as there is something I discovered. – Kalenji Jul 25 '18 at 10:16