Combine 2 SELECT statements with filled gaps

Question

I have this SELECT:

SELECT
    m.`maschine-name` AS 'byMaschine',
    q.`mname` AS 'byMName'
FROM
    `qualitaet` q
INNER JOIN
    maschinen m ON m.maschine = q.maschine
WHERE
    q.`status`='1'
GROUP BY
    concat(q.maschine, q.mname)

and get this result:

| maschine-name | mname |
|     TYP 1     |   0   |
|     TYP 2     |   3   |
|     TYP 2     |   4   |
|     TYP 3     |   0   |
|     TYP 4     |   0   |

see SQL Fiddle here

Then i have nearly the same SELECT with additional COUNT and Datefilter:

SELECT
    m.`maschine-name` AS 'byMaschine',
    q.`mname` AS 'byMName',
COUNT(*) AS 'total'
FROM
    `qualitaet` q
INNER JOIN
    maschinen m ON m.maschine = q.maschine
WHERE
    q.`created`>=NOW() - INTERVAL 2 DAY
AND
    q.`status`='1'
GROUP BY
    concat(q.maschine, q.mname)

and get this result:

| maschine-name | mname | total |
|     TYP 2     |   3   |   1   |
|     TYP 3     |   0   |   2   |

see SQL Fiddle here

The 2nd SELECT doesn't give me all information. I need a mix from both SELECTS

The 2nd SELECT should look like this result:

| maschine-name | mname | total |
|     TYP 1     |   0   |   0   |
|     TYP 2     |   3   |   1   |
|     TYP 2     |   4   |   0   |
|     TYP 3     |   0   |   2   |
|     TYP 4     |   0   |   0   |

Is it possible to RIGHT JOIN with 2 SELECTS? Or is there another way to get the result?

score 2 · Accepted Answer · answered Dec 31 '15 at 14:41

2

Use conditional aggregation:

SELECT m.`maschine-name` AS byMaschine, q.`mname` AS byMName,
       sum(q.created >= NOW() - INTERVAL 2 DAY) as Total
FROM qualitaet q INNER JOIN
     maschinen m
    ON m.maschine = q.maschine
WHERE q.status = '1'
GROUP BY q.maschine, q.mname;

Other suggestions:

There is no need to concatenate the grouping columns in the GROUP BY -- unless you really, really intend to do this (which I doubt).
If status is numeric, don't use single quotes for the constant.
Don't use single quotes for column aliases. In fact, your aliases don't need any quotes at all.

answered Dec 31 '15 at 14:41

Gordon Linoff

1,242,037
58
646
786

Wow! That was fast! And works perfect! Thank you! HAPPY NEW YEAR ;) – bernte Dec 31 '15 at 15:04
I found a little problem. i cleared all q.status fields to 1 and added only 1 row with status=2. When i now select with satus=2 i get only one TYP. Do you have another idea that also status=2 have all TYP listed? see the sqlfiddle here http://sqlfiddle.com/#!9/937d4/1 – bernte Jan 01 '16 at 01:32
@bernie . . . There is no column called `TYP` in the question, so I don't understand your comment. Perhaps you should ask another question. – Gordon Linoff Jan 01 '16 at 16:53

Combine 2 SELECT statements with filled gaps

1 Answers1