не могу сделать запрос

[mcavalon]

доброй ночи, вы форумчане!

ситуация следующая, есть запрос

SELECT 
            shop_items_catalog_item_id as item_id,
            shop_items_catalog_marking as item_num_in,
            shop_items_catalog_name as item_name,
            shop_vendorcode as vendor,
            
            round(((SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 3 AND
                shop_items_catalog_item_id = item_id) 
            + 
            (SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 4 AND
                shop_items_catalog_item_id = item_id) 
            +
            (SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 5 AND
                shop_items_catalog_item_id = item_id
            )),0) as total_count, 
            
            round((SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 4 AND
                shop_items_catalog_item_id = item_id
            ),0) as bm,
            
            round((SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 5 AND
                shop_items_catalog_item_id = item_id
            ),0) as mm,
            
            round((SELECT shop_warehouse_items_count
            FROM shop_warehouse_items_table
            WHERE 
                shop_warehouse_id = 3 AND
                shop_items_catalog_item_id = item_id
            ),0) as sklad,
            
            (SELECT shop_prices_to_item_value
            FROM shop_prices_to_item_table
            WHERE 
                shop_list_of_prices_id = 4 AND
                shop_items_catalog_item_id = item_id
            ) as price_p,
            
            (SELECT shop_prices_to_item_value
            FROM shop_prices_to_item_table
            WHERE 
                shop_list_of_prices_id = 5 AND
                shop_items_catalog_item_id = item_id
            ) as price_r

        FROM 
            `shop_items_catalog_table`
        WHERE
            users_id = 19

как мне сделать в нем итоговую строку по 5-10 колонкам?
помогите пожалуйста, никак не получается сделать

[devote]

ох нихрена себе жесть... таких запросов я еще не делал... что-то слишком не оптимизировано.

[ksa]

как мне сделать в нем итоговую строку по 5-10 колонкам?

1. Повторить запрос без колонок 1-4, а "5-10" будут в sum()...
2. Пробежаться в цикле по полученой таблице, суммируя нужные колонки...

[mcavalon]

мне нужно именно этот запрос доработать, другие создать нельзя. я так понимаю через union но у меня не получается

[ksa]

через union

Для его использования нужно совпадение количества столбцов и их однотипность.

Повтори еще раз этот же запрос, но вместо

SELECT 
            shop_items_catalog_item_id as item_id,
            shop_items_catalog_marking as item_num_in,
            shop_items_catalog_name as item_name,
            shop_vendorcode as vendor,
            ...

помести константы такого же типа, как в первом селекте.

P.S.
Но запрос действительно смотрится плохо... Сделай хранимую процедуру для запроса такого типа

SELECT shop_warehouse_items_count
FROM shop_warehouse_items_table
WHERE 
        shop_warehouse_id = ? AND
        shop_items_catalog_item_id = ?

Даёшь процедуре два параметра, а тебе в обратную нужные значения...
Всё лучше будет смотреться...

[mcavalon]

сделал так:

$param['data'][] = "
        SELECT
            shop_items_catalog_item_id as item_id,
            shop_items_catalog_marking as item_num_in,
            shop_items_catalog_name as item_name,
            shop_vendorcode as vendor,
            round((
                (SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 3 AND shop_items_catalog_item_id = item_id) +
                (SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 4 AND shop_items_catalog_item_id = item_id) +
                (SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 5 AND shop_items_catalog_item_id = item_id)
            ),0) as total_count,
            round((SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 4 AND shop_items_catalog_item_id = item_id),0) as bm,
            round((SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 5 AND shop_items_catalog_item_id = item_id),0) as mm,
            round((SELECT shop_warehouse_items_count FROM shop_warehouse_items_table WHERE shop_warehouse_id = 3 AND shop_items_catalog_item_id = item_id),0) as sklad,
            (SELECT shop_prices_to_item_value FROM shop_prices_to_item_table WHERE shop_list_of_prices_id = 4 AND shop_items_catalog_item_id = item_id) as price_p,
            (SELECT shop_prices_to_item_value FROM shop_prices_to_item_table WHERE shop_list_of_prices_id = 5 AND shop_items_catalog_item_id = item_id) as price_r
        FROM `shop_items_catalog_table`
        WHERE 1 {FILTER_STRING} {ORDER_STRING} {LIMIT}
    ";
	$param['data'][] = "
        SELECT
            '' as item_id,
            '' as item_num_in,
            'Сумма' as item_name,
            '' as vendor,
            round(SUM(w2.shop_warehouse_items_count) + SUM(w3.shop_warehouse_items_count) + SUM(w4.shop_warehouse_items_count), 0) as total_count,
            round(SUM(w2.shop_warehouse_items_count), 0) as bm,
            round(SUM(w3.shop_warehouse_items_count), 0) as mm,
            round(SUM(w4.shop_warehouse_items_count), 0) as sklad,
            round(SUM(p1.shop_prices_to_item_value), 0) as price_p,
            round(SUM(p2.shop_prices_to_item_value), 0) as price_r
        FROM `shop_items_catalog_table` as i
            LEFT JOIN shop_warehouse_items_table as w2 ON  i.shop_items_catalog_item_id = w2.shop_items_catalog_item_id AND w2.shop_warehouse_id = 4
            LEFT JOIN shop_warehouse_items_table as w3 ON  i.shop_items_catalog_item_id = w3.shop_items_catalog_item_id AND w3.shop_warehouse_id = 5
            LEFT JOIN shop_warehouse_items_table as w4 ON  i.shop_items_catalog_item_id = w4.shop_items_catalog_item_id AND w4.shop_warehouse_id = 3
            LEFT JOIN shop_prices_to_item_table as p1 ON  i.shop_items_catalog_item_id = p1.shop_items_catalog_item_id AND p1.shop_list_of_prices_id = 4
            LEFT JOIN shop_prices_to_item_table as p2 ON  i.shop_items_catalog_item_id = p2.shop_items_catalog_item_id AND p2.shop_list_of_prices_id = 5
        WHERE 1 {FILTER_STRING} LIMIT 1
    ";