Форум dkLab и Denwer
Здесь общаются Web-разработчики.
Генеральный спонсор:
Хостинг «Джино»

Группировка данных при левом объединении неверно агрегирует значения NULL (mus)
Author Message
mus
Участник форума



Joined: 06 Mar 2005
Posts: 130
Карма: -10
   поощрить/наказать


PostPosted: Fri Feb 23, 2007 12:47 am (написано за 14 минут 11 секунд)
   Post subject: Группировка данных при левом объединении неверно агрегирует значения NULL
Reply with quote

Задача следующая - вывести одним запросом список стран с суммой денег, которая была выручена дилерами, принадлежащими этим странам, при этом следует учесть, что вывести следует все страны, а если в той или иной стране дилеры ничего не приобрели, то вывести NULL. Стоимость подсчитываем лишь в тех заказах, где оплата уже была завершена.

Структура БД (привожу лишь те атрибуты, что участвуют в запросе)
______________
Sale (продажи)
---
sale_id
sale.nds (0,1,2,3)
sale.country_id
sale.pack // индикатор оплаты счета, если оплачен - 1, нет - 0
---
______________

Sale_client (характеристическая таблица для осущ. связи "многие ко многим" между товаром и счетом)
---
sale_id
price // стоимость без учета ндс
price_nds // стоимость с учетом НДС
quantity // кол-во
---
______________

Country
---
country_id
country_title
---
______________


В итоге мы должны получить таблицу вида

Germany 119
Irland 86
Russia 150
Brazil NULL
Austria NULL


Делал так:
Code (SQL): скопировать код в буфер обмена
SELECT country.country_id, country.title,
(SELECT ROUND(SUM(IF((sale.nds =1 OR sale.nds =3), sale_client.price_nds, sale_client.price ) * sale_client.quantity) , 2)
FROM sale_client
WHERE sale.sale_id = sale_client.sale_id
AND sale.pack =1) AS summa
FROM country LEFT JOIN sale ON country.country_id = sale.country_id
GROUP BY country.title
Выводит следующее

Germany NULL
Russia NULL
и т.д. все NULL, однако выводить должен у Germany 119

Убираю из запроса группировку, получаю след. картину

Germany NULL
Germany NULL
Germany 119
Russia NULL
Russia NULL

Я так понял, что проблемы в том, что суммируя NULL и 119 мы получаем NULL.

Два вопроса:
1) Как можно избежать суммирование NULL полей?
2) Правильно ли я составил запрос? И как бы составили Вы?
Back to top
View user's profile Send private message
mus
Участник форума



Joined: 06 Mar 2005
Posts: 130
Карма: -10
   поощрить/наказать


PostPosted: Fri Feb 23, 2007 1:33 am (спустя 46 минут; написано за 41 секунду)
   Post subject:
Reply with quote

Вообще канает ещё и этот вариант, но насколько он валиден?
Code (SQL): скопировать код в буфер обмена
SELECT country.country_id, country.title, (

SELECT ROUND( SUM(
IF (
(
sale.nds =1
OR sale.nds =3
), sale_client.price_nds, sale_client.price ) * sale_client.quantity ) , 2
)
FROM sale_client, sale
WHERE sale.sale_id = sale_client.sale_id
AND country.country_id = sale.country_id
AND sale.pack =1
) AS summa
FROM country
Back to top
View user's profile Send private message
Владимир Хоменко
Участник форума



Joined: 11 Jun 2004
Posts: 427
Карма: 42
   поощрить/наказать

Location: Николаев, Украина

PostPosted: Fri Feb 23, 2007 3:08 pm (спустя 13 часов 35 минут; написано за 6 минут 42 секунды)
   Post subject:
Reply with quote

mus wrote:
Я так понял, что проблемы в том, что суммируя NULL и 119 мы получаем NULL.
Вы все верно поняли, проверяйте данные на NULL:
Code (SQL): скопировать код в буфер обмена
SELECT IF(ISNULL(sale_client.sale_id), 0, ROUND(SUM(IF((sale.nds =1 OR sale.nds =3), sale_client.price_nds, sale_client.price ) * sale_client.quantity) , 2))
Я бы написал все не используя подзапросов вообще(выборку на работоспособность не проверял, но принцип должен быть ясен):
Code (SQL): скопировать код в буфер обмена
SELECT
country.country_id,
country.title,
SUM(ISNULL(sale_client.sale_id), 0, ROUND(SUM(IF((sale.nds =1 OR sale.nds =3), sale_client.price_nds, sale_client.price ) * sale_client.quantity) , 2)) AS summa
FROM country
LEFT JOIN sale ON (country.country_id = sale.country_id AND sale.pack = 1)
LEFT JOIN sale_client USING(sale_id)
GROUP BY country.country_id
Back to top
View user's profile Send private message Send e-mail
mus
Участник форума



Joined: 06 Mar 2005
Posts: 130
Карма: -10
   поощрить/наказать


PostPosted: Fri Feb 23, 2007 8:12 pm (спустя 5 часов 4 минуты; написано за 1 минуту 15 секунд)
   Post subject:
Reply with quote

Владимир Хоменко

Благодарю за ответ.

Не могли бы Вы объяснить мне, как работает двойное левое объединение и суть оператора USING, если не сложно.
Несколько не улавливаю логику... =(
Back to top
View user's profile Send private message
Владимир Хоменко
Участник форума



Joined: 11 Jun 2004
Posts: 427
Карма: 42
   поощрить/наказать

Location: Николаев, Украина

PostPosted: Sat Feb 24, 2007 12:49 pm (спустя 16 часов 36 минут; написано за 7 минут 7 секунд)
   Post subject:
Reply with quote

dev.mysql.com/doc/refman/5.0/en/join.html
Quote:
The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables
Code (SQL): скопировать код в буфер обмена
a LEFT JOIN b USING(id)
эквивалентно
Code (SQL): скопировать код в буфер обмена
a LEFT JOIN b ON a.id = b.id
Насчет двойного LEFT JOIN, попробуйте почитать мануал, поиграться с результами выборки, и если ничего не прояснится(в чем я сомневаюсь), возвращайтесь, попробуем помочь.
Back to top
View user's profile Send private message Send e-mail
mus
Участник форума



Joined: 06 Mar 2005
Posts: 130
Карма: -10
   поощрить/наказать


PostPosted: Sat Feb 24, 2007 2:33 pm (спустя 1 час 43 минуты; написано за 33 секунды)
   Post subject:
Reply with quote

Владимир Хоменко

Снова благодарю Вас, Владимир. Приглашаю поучаствовать в теме forum.dklab.ru/viewtopic.php?t=24719
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic All times are GMT + 3 Hours
Page 1 of 1    Email to a Friend.
You cannot post new topics in this forum. You cannot reply to topics in this forum. You cannot edit your posts in this forum. You cannot delete your posts in this forum. You cannot vote in polls in this forum. You cannot attach files in this forum. You can download files in this forum.
XML