MySQL: How to use main query variable inside sub query WHERE condition

Question

Here is my Table "assets"

+----+----------------------------+-------+-----------+
| id | model                      | grade | warehouse |
+----+----------------------------+-------+-----------+
|  1 | Optiplex 9030 AIO i5       |     2 |         2 |
|  2 | Optiplex 9030 AIO i3 Touch |     2 |         2 |
|  3 | Optiplex 9030 AIO i7       |     2 |         1 |
|  4 | Optiplex 9030 AIO i5 Touch |     1 |         1 |
|  5 | Optiplex 9030 AIO i7       |     1 |         1 |
|  6 | Optiplex 9030 AIO i7       |     2 |         1 |
|  7 | Optiplex 9030 AIO i7       |     1 |         2 |
|  8 | Optiplex 9030 AIO i5 Touch |     2 |         2 |
|  9 | Optiplex 9030 AIO i5       |     1 |         1 |
| 10 | Optiplex 9030 AIO i5       |     2 |         2 |
+----+----------------------------+-------+-----------+

What I am trying to do is

SELECT id, model, grade, @ids := GROUP_CONCAT(id) AS ids, 
(SELECT COUNT(*) FROM assets WHERE id IN (@ids) AND warehouse = 1 ) AS w_1_count,
(SELECT COUNT(*) FROM assets WHERE id IN (@ids) AND warehouse = 2 ) AS w_2_count
FROM `assets` GROUP BY model, grade

its give me result like this

+----+----------------------------+-------+------+-----------+-----------+
| id | model                      | grade | ids  | w_1_count | w_2_count |
+----+----------------------------+-------+------+-----------+-----------+
|  2 | Optiplex 9030 AIO i3 Touch |     2 | 2    |         1 |         0 |
|  9 | Optiplex 9030 AIO i5       |     1 | 9    |         0 |         1 |
|  1 | Optiplex 9030 AIO i5       |     2 | 1,10 |         1 |         0 |
|  4 | Optiplex 9030 AIO i5 Touch |     1 | 4    |         0 |         1 |
|  8 | Optiplex 9030 AIO i5 Touch |     2 | 8    |         1 |         0 |
|  5 | Optiplex 9030 AIO i7       |     1 | 5,7  |         0 |         1 |
|  3 | Optiplex 9030 AIO i7       |     2 | 3,6  |         1 |         0 |
+----+----------------------------+-------+------+-----------+-----------+

expected result is: which I want to get? but no luck

+----+----------------------------+-------+------+-----------+-----------+
| id | model                      | grade | ids  | w_1_count | w_2_count |
+----+----------------------------+-------+------+-----------+-----------+
|  2 | Optiplex 9030 AIO i3 Touch |     2 | 2    |         0 |         1 |
|  9 | Optiplex 9030 AIO i5       |     1 | 9    |         1 |         0 |
|  1 | Optiplex 9030 AIO i5       |     2 | 1,10 |         0 |         2 |
|  4 | Optiplex 9030 AIO i5 Touch |     1 | 4    |         1 |         0 |
|  8 | Optiplex 9030 AIO i5 Touch |     2 | 8    |         0 |         1 |
|  5 | Optiplex 9030 AIO i7       |     1 | 5,7  |         1 |         1 |
|  3 | Optiplex 9030 AIO i7       |     2 | 3,6  |         2 |         0 |
+----+----------------------------+-------+------+-----------+-----------+

when I select @ids variable for display its give me weird result set

SELECT id, model, grade, @ids := GROUP_CONCAT(id) AS ids, @ids, 
(SELECT COUNT(*) FROM assets WHERE id IN (@ids) AND warehouse = 1 ) AS w_1_count,
(SELECT COUNT(*) FROM assets WHERE id IN (@ids) AND warehouse = 2 ) AS w_2_count
FROM `assets` GROUP BY model, grade

+----+----------------------------+-------+------+------+-----------+-----------+
| id | model                      | grade | ids  | @ids | w_1_count | w_2_count |
+----+----------------------------+-------+------+------+-----------+-----------+
|  2 | Optiplex 9030 AIO i3 Touch |     2 | 2    | 3,6  |         1 |         0 |
|  9 | Optiplex 9030 AIO i5       |     1 | 9    | 2    |         0 |         1 |
|  1 | Optiplex 9030 AIO i5       |     2 | 1,10 | 9    |         1 |         0 |
|  4 | Optiplex 9030 AIO i5 Touch |     1 | 4    | 1,10 |         0 |         1 |
|  8 | Optiplex 9030 AIO i5 Touch |     2 | 8    | 4    |         1 |         0 |
|  5 | Optiplex 9030 AIO i7       |     1 | 5,7  | 8    |         0 |         1 |
|  3 | Optiplex 9030 AIO i7       |     2 | 3,6  | 5,7  |         1 |         0 |
+----+----------------------------+-------+------+------+-----------+-----------+

Questions:

1.) how to achieve my expected result set which is bind with @ids & one another condition?

2.) why @ids column showing wrong value of next row where it should be same as group_concat ids? I don't know where I am wrong

here I have attached a SQL fiddle for quick help

http://sqlfiddle.com/#!9/9e3e5d/1/0

Answer 1

I think you just want conditional aggregation here. The solution below joins to a subquery which aggregates by model and grade, turning out the lowest id per group, the CSV list of ids, along with the two conditional warehouse counts.

SELECT
    a2.min_id AS id,
    a1.model,
    a1.grade,
    a2.ids,
    a2.w_1_count,
    a2.w_2_count
FROM assets a1
INNER JOIN
(
    SELECT
        model,
        grade,
        MIN(id) AS min_id,
        GROUP_CONCAT(id) AS ids,
        SUM(warehouse = 1) AS w_1_count,
        SUM(warehouse = 2) AS w_2_count
    FROM assets
    GROUP BY model, grade
) a2
    ON a1.model = a2.model AND
       a1.grade = a2.grade AND
       a1.id = a2.min_id;

Here is a link to your updated demo using the above query:

Demo