SQL IN GROUP_CONCAT

[ש.ב.ח.]

SELECT 
  ((SELECT sum(order_items.qty) from order_items where order_items.order_id in (GROUP_CONCAT(orders.id)))) as qty,
  GROUP_CONCAT(orders.id) as orders_ids
FROM customers
left join orders on orders.customer_id = customers.id

group by customers.id

התוצאה של הselect השני היא 1,2,3 ואף על פי כן הראשון שאמור להזין את אותה תוצאה לתוך הin מחזיר תוצאה לא נכונה לאמת ואין סיכום של הqty.

האם אכן לא שייך לבצע כך?

[dovid]

לכתחילא בכלל לא הייתי חושב על IN, זו לא אפשרות נורמטיבית לכאלה צרכים.
ככה לא עובד?

SELECT 
customers.id,
SUM(order_items.qty) as qty,
GROUP_CONCAT(orders.id) as orders_ids
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
LEFT JOIN order_items  ON order_items.order_id = orders.id
group by customers.id

לגבי IN יש היגיון שזה לא יעבוד, אני רואה אכן ככה פה: https://stackoverflow.com/a/20000654/1271037
שם כתוב שfind_in_set כן עובד טוב.

[ש.ב.ח.]

@dovid אתה צודק שבשאילתה של הדוגמא שלי זה יכול להסתדר כך
אבל במקורית שאמורה להיות כך:

SELECT 
  customers.id,
  customers.name,
  customers.email,
  customers.phone,
  sum(order_items.qty) as qty,
  sum(payments.amount) as payup,
  sum(order_items.price - payments.amount) as balance
FROM customers
left join orders on orders.customer_id = customers.id
left join order_items on orders.id = order_items.order_id
LEFT join payments on payments.order_id = orders.id

group by customers.id

זה וודאי לא יהיה טוב מכיוון שיש כאן רשומות שנכפלות והתוצאה של הpayup לא תהיה נכונה, כמובן שגם הbalance.

ואז מה שאני רוצה לעשות זה כך:

SELECT 
  customers.id,
  customers.name,
  customers.email,
  customers.phone,
  sum(order_items.qty) as qty,
  sum(order_items.price - (SELECT COALESCE(sum(payments.amount), 0) from payments join orders on orders.id = payments.order_id where orders.customer_id = customers.id)) as balance,
  (SELECT sum(payments.amount) from payments where payments.order_id in (GROUP_CONCAT(orders.id))) as payup
FROM customers
left join orders on orders.customer_id = customers.id
left join order_items on order_items.order_id = orders.id

group by customers.id

[dovid]

  MIN(sum(order_items.price - payments.amount)) as balance

ככה אמור להיות טוב,
וגם אם לא לא צריך להגיע לIN, עושים שאילתה פנימית אחרת.
אם הייתי יודע בדיוק מה אתה רוצה הייתי כותב הכל אבל לא הכי ברור לי.

[איש נחמד]

זו פונקציית צבירה, ומשכך יתכן ששימוש ב-HAVING במקום WHERE יפתור את הבעיה

[ש.ב.ח.]

@dovid כתב בSQL IN GROUP_CONCAT:

MIN(sum(order_items.price - payments.amount)) as balance
ככה אמור להיות טוב,

לצערי ישנה שגיאה: Invalid use of group function

@dovid כתב בSQL IN GROUP_CONCAT:

אם הייתי יודע בדיוק מה אתה רוצה הייתי כותב הכל אבל לא הכי ברור לי.

מנסה להסביר בעברית:
יש לי טבלאות לקוחות, הזמנות ותשלומים
לכל הזמנה יכולים להיות פרטים מרובים שנשמרים בטבלת פריטי הזמנה, המחיר נשמר בפריט.

מה שאני רוצה עכשיו
בטבלת הלקוחות לקבל את:
סיכום הפריטים של פריטי ההזמנה (בעצם כל פריט יכול להיות כמה יחי'),
הסכום שהלקוח שילם כבר (כל התשלומים שלו ).
המאזן שלו (המחירים של כל פריט ההזמנות בקיזוז כל התשלומים שלו עליהם).

@dovid כתב בSQL IN GROUP_CONCAT:

וגם אם לא לא צריך להגיע לIN, עושים שאילתה פנימית אחרת.

היה נראה לי מיותר ליצור שאילתה פנימית נוספת בשעה שכל הנתונים כבר כאן ע"י הjoins...

[dovid]

@ש-ב-ח כתב בSQL IN GROUP_CONCAT:

לצערי ישנה שגיאה: Invalid use of group function

אכן, טעות שלי, אי אפשר לבצע MIN על SUM וזה גם לא היה נכון לו זה היה אפשרי.

סיכום הפריטים של פריטי ההזמנה (בעצם כל פריט יכול להיות כמה יחי'),
הסכום שהלקוח שילם כבר (כל התשלומים שלו ).
המאזן שלו (המחירים של כל פריט ההזמנות בקיזוז כל התשלומים שלו עליהם).

ועדיין איני יודע אם שורה לכל הזמנה, או שורה אחת לכולם.
זה שורה אחת ללקוח (כל ההזמנות וכל התשלומים מכווצים בשורה אחת),
אני לא יודע אם זה עובד (אולי יש טעויות תחביר או טעות לוגית שלי או שגם לא הבנתי את הצרכים או את המבנה) אבל זה הכיוון הנכון, לבודד תצוגות (למשל פה השאילתה הפנימית זה תצוגת הזמנות עם סכומיהם), ולעבוד מולם.

SELECT 
  customers.id,
  customers.name,
  customers.email,
  customers.phone,
  Orders_with_sum.order_id
  SUM(Orders_with_sum.Sum_All_Items) AS Sum_Orders,
  SUM(payments.amount) AS Sum_Payments,
  SUM(Orders_with_sum.Sum_All_Items) - SUM(payments.amount) AS balance ,
FROM customers
LEFT JOIN 
(
    SELECT 
    orders.id AS order_id
    orders.customer_id AS customer_id
    SUM(order_items.price) AS Sum_All_Items
    FROM orders 
    LEFT JOIN order_items on orders.id = order_items.order_id
    GROUP BY orders.id
) AS Orders_with_sum ON Orders_with_sum.customer_id = customers.id
LEFT JOIN payments ON payments.order_id = orders.id
GROUP BY customers.id