Merge 2 queries with different WHERE clause

Question

I have the following query:

select
*
from 
(
SELECT art_titel as at, COUNT(*) as Number1
FROM artikel_views 
WHERE user_id != ''  
GROUP BY art_titel 
ORDER BY Number1 desc
) as FirstSet
join
(
SELECT art_titel as at, COUNT(*) as Number2
FROM artikel_views 
WHERE user_id = ''  
GROUP BY art_titel 
ORDER BY Number2 desc
) as SecondSet
on FirstSet.at = SecondSet.at

My HTML table:

<table>
  <tr>
    <td>Art Titel</td>
    <td>user_id is NULL</td>
    <td>user_id is not NULL</td>
  </tr>

<?php while($row = mysql_fetch_assoc($res)) { ?>

  <tr>
    <td><?php echo $row['at']; ?></td>
    <td><?php echo $row['Number2']; ?></td>
    <td><?php echo $row['Number1']; ?></td>
  </tr>

<?php } ?>

</table>

What i do is taking the values from $row['Number1'] and $row['Number 2'] in combination with the art_titel. The result from this query is not good. I have only the art_titel and art_views from the matches on art_titel in both queries but it's not good. I want all items in both ways.

I have tried also a UNION but is isn't working my way.

It's hard to tell without any sample data or expected results (which I recommend you include next time you ask a question. You could edit this one if you're ambitious) but you could try a UNION and see if that works. — AdamMc331, Nov 02 '14 at 20:03
I have tried a UNION a couple of seconds ago and it's also not working my way. — Brecht S, Nov 02 '14 at 20:05
Those echo need to be put in a WHILE loop,so I guess the number1 and number2 for the last groups happen to be the same,so that`s why you get the same values. — Mihai, Nov 02 '14 at 20:28
I have updated my question again. I had used a while but was forgot to copy it in my question. — Brecht S, Nov 02 '14 at 20:31
I think you also need to echo the table otherwise you`ll have the same columns overwritten for each iteration.http://stackoverflow.com/a/17902527/1745672 — Mihai, Nov 02 '14 at 20:33
I do the same thing with my loop of your example. I don't see a problem with my loop. — Brecht S, Nov 02 '14 at 20:37
I have updated my HTML table so you can better see what i mean, see the headers of the table. Now i have in user_id is NULL and in the user_id is not NULL the same results with your query but it's not correct. — Brecht S, Nov 02 '14 at 20:45
I think it's working now with the right values but the sort is not correct. The ORDER BY Number1 desc and Number2 desc is not working. — Brecht S, Nov 03 '14 at 09:01
I see the problem here. It's sorting on Number1 first, then Number2. So it's good and works correct. — Brecht S, Nov 03 '14 at 09:06
@ Mihai: Can you please take a look at this question also http://stackoverflow.com/questions/26615254/mysql-inner-join-on-2-tables-dont-work — Brecht S, Nov 03 '14 at 13:22

Mihai · Accepted Answer · 2014-11-02T20:42:34.497

0

No need for 2 queries,just use a CASE condition.

SELECT art_titel as at, SUM(CASE WHEN user_id != '' THEN 1 ELSE 0 END) as Number1,
SUM(CASE WHEN user_id = '' THEN 1 ELSE 0 END) as Number2
FROM artikel_views 
GROUP BY art_titel 
ORDER BY Number1 desc,Number2 desc

edited Nov 02 '14 at 20:42

answered Nov 02 '14 at 20:10

Mihai

26,325
7
66
81

If i use $row['Number1'] and $row['Number2'] are the values the same in combination with art_titel and the values are not the same in my database. So what is going wrong? – Brecht S Nov 02 '14 at 20:15
@ Mihai: I want to populate my HTML table with the output of the query. For that i use $row['at'] for the values art_titel and $row[Number1'] for the values in the first column of my table and $row['Number2'] for the values in the second column of my table. The values from Number 1 and Number2 are the same in my table now. But in my database it's not. – Brecht S Nov 02 '14 at 20:21
1

@Mihai: The COUNT count aggregate is incremented for all non-NULL values, so `Number1` and `Number2` are going to the same. Either replace the `ELSE 0` with `ELSE NULL`, or use `SUM` aggregate instead of `COUNT`. – spencer7593 Nov 02 '14 at 20:40
@spencer7593 http://gp1.wac.edgecastcdn.net/802892/production_public/Artist/3621518/image/small/1378902809_545016088873462_1511234723_o.jpg Thanks. – Mihai Nov 02 '14 at 20:43

Merge 2 queries with different WHERE clause

1 Answers1