CODEWITHSUNDEEP
sqlsql-server-2008joincountleft-join
eddy
eddy

Reputation: 518

SQL count joins distinct by multiple columns

I have a problem where I need to JOIN on the same table multiple time and count the JOINS.

here is the database setup (SQL Fiddle ):

CREATE TABLE state
(
  [t_id] int,
  [true_id] int,
  [false_id] int,
  [msg] varchar(32) 
);

INSERT INTO state
  (t_id, true_id, false_id, msg)
VALUES
  (5, 6, 7, 'CASE_1'),
  (10, 11, 12, 'CASE_2'),
  (20, 21, 22, 'CASE_N'),
  (30, 31, 32, 'FOOOO');

CREATE TABLE step
(
      [id] int,
      [f_id] int,
      [state_type] int,
      [state_value] int
);

INSERT INTO step
    (id,f_id,state_type, state_value)
VALUES
    (1, 5, 5, 7),
    (2, 5, 5, 7),
    (3, 5, 5, 6),

    (4, 5, 10, 12),
    (5, 5, 10, 12),
    (6, 5, 10, 11),

    (7, 6, 10, 12),
    (8, 6, 10, 12),

    (9, 7, 20, 21),
    (10, 7, 20, 21),

    (11, 7, 30, 32),
    (12,7, 30, 31);

here is my current query:

SELECT state.msg, 
  COUNT(state_true.true_id) AS Trues,
  COUNT(state_false.false_id) AS Falses
FROM state
  INNER JOIN step ON state.t_id = step.state_type
  LEFT OUTER JOIN state AS state_true ON step.state_value = state_true.true_id
  LEFT OUTER JOIN state AS state_false ON step.state_value = state_false.false_id
GROUP BY state.msg, step.f_id

And here what I get:

msg     Trues   Falses
CASE_1  1       2
CASE_2  1       2
CASE_2  0       2
CASE_N  2       0
FOOOO   1       1

And here what i need:

msg     Trues   Falses
CASE_1  1       0
CASE_2  1       1
CASE_N  1       0
FOOOO   1       0

For Explanation:

I need to count how many trues and fails are per state_type and f_id combination.

There are 6 entries with the f_id = 5 -> (1,2,3,4,5,6). If there is a entry with the same (f_id,state_type) combination, only the last one should be counted. So for f_id 5 the entries 1,2,4,5 should not be taken into the count, as they are overwritten by 3 and 6.

So after processing the first 6 entries there should be CASE_1 true => 1 false => 0 and CASE_2 true => 1 false => 0

__ EDIT __

 TABLE step:

(1, 5, 5, 7),  -- do not count
(2, 5, 5, 7),  -- do not count
(3, 5, 5, 6),  -- this is the last entry with 
               -- (f_id,state_type) => (5,5) combination. 
               -- it overwrites the 2 previous ones => count CASE_1 true

(4, 5, 10, 12), -- do not count
(5, 5, 10, 12), -- do not count
(6, 5, 10, 11), -- count CASE_2 true

(7, 6, 10, 12), -- do not count
(8, 6, 10, 12), -- count CASE_2 false

(9, 7, 20, 21),  -- do not count
(10, 7, 20, 21), -- count CASE_N false

(11, 7, 30, 32), -- do not count
(12,7, 30, 31);  -- count FOOOO true

Upvotes: 3

Views: 103

Answers (1)

jpw
jpw

Reputation: 44891

I'm not sure I fully understand your intent, but maybe the query below might be what you want? Given your sample data it seems to produce the right output.

SELECT state.msg, 
  SUM(CASE WHEN true_id  = state_value THEN 1 ELSE 0 END) AS Trues,
  SUM(CASE WHEN false_id = state_value THEN 1 ELSE 0 END) AS Falses
FROM state
JOIN step ON state.t_id = step.state_type
JOIN (SELECT MAX(id) mid FROM step GROUP BY f_id, state_type) a ON a.mid = step.id
GROUP BY state.msg;

Please give it a try. If I misunderstood I'll remove the answer.

Upvotes: 2

Related Questions