K
K
Kirill Petrov2017-05-27 12:20:50
MySQL
Kirill Petrov, 2017-05-27 12:20:50

How to deal with NULL fields after JOIN with GROUP BY in MySQL?

Greetings!
Please tell me how to get the right results. There are signs:
For convenience, uploaded to the sandbox sqlfiddle.com/#!9/1e9b8c/19

CREATE TABLE sour
  (`id` int, `source` varchar(7), `description` varchar(55))
;
  
INSERT INTO sour
  (`id`, `source`, `description`)
VALUES
  (1, 'raz', 'aza1'),
  (2, 'dva', 'aza2'),
  (3, 'tri', 'aza3'),
  (4, 'chetire', 'aza4')
;

CREATE TABLE app
  (`id` int, `sour_id` int, `name` varchar(55))
;
  
INSERT INTO app
  (`id`, `sour_id`, `name`)
VALUES
  (1, 1, 'zzz1'),
  (2, 3, 'zzz2'),
  (3, 3, 'zzz3'),
  (4, 1, 'zzz4'),
  (5, 3, 'zzz5'),
  (6, 4, 'zzz6')
;

When such a request is made
SELECT 
  sour.source, 
  COUNT(app.sour_id) as `c` 
FROM `app` 
  RIGHT JOIN `sour` ON sour.id = app.sour_id 
  WHERE app.id > 2
  GROUP BY(app.sour_id);

I receive
raz	1
tri	2
chetire	1

How to display that dva = 0 to the number of records?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
R
RidgeA, 2017-05-27
@RidgeA

COUNT(app.id) as `c`

R
redya69, 2017-05-31
@redya69

So?)

SELECT 
  sour.source, 
  COUNT(app.sour_id) as `c` 
FROM `sour` 
  LEFT JOIN `app` ON sour.id = app.sour_id AND app.id > 2
  GROUP BY(sour.id);

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question