Sum specific rows

  • Resolved AndreB.

    (@andreb-1)


    2 years, 9 months ago

    Hello

    I have a query where the columns “Ausfahrt”, “Kontoinhaber”, “Gesamtsumme”, “Vorname Teilnehmer”, “Order_ID” are queried. The data comes from the orders of the participants.

    Now, in the query, I would like to summarize the rows as follows:

    If the “Order_ID” is the same, the lines “Vorname Teilnehmer” should be combined in one line and separated by a “,” for example.

    This is my code so far:

    SELECT	zkzCrojUwoocommerce_order_items.order_id as 'Order_id'
,		zkzCrojUwoocommerce_order_items.order_item_name as 'Order_item_name'
,		zkzCrojUpostmeta.meta_value as 'Kontoinhaber'
,		zkzCrojUpostmeta2.meta_value as 'Gesamtsumme'
,		zkzCrojUwoocommerce_order_itemmeta.meta_value as 'Teilnehmer'
FROM	zkzCrojUpostmeta
INNER JOIN zkzCrojUwoocommerce_order_items ON zkzCrojUpostmeta.post_id = zkzCrojUwoocommerce_order_items.order_id
INNER JOIN zkzCrojUpostmeta zkzCrojUpostmeta2 ON zkzCrojUpostmeta2.post_id = zkzCrojUpostmeta.post_id
INNER JOIN zkzCrojUwoocommerce_order_itemmeta ON zkzCrojUwoocommerce_order_itemmeta.order_item_id = zkzCrojUwoocommerce_order_items.order_item_id
WHERE	(zkzCrojUpostmeta.meta_key = '_direct_debit_holder' AND zkzCrojUpostmeta2.meta_key = '_order_total' AND zkzCrojUwoocommerce_order_itemmeta.meta_key = 'vorname')

    Could you help me with my problem?

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Contributor Kim L

    (@kimmyx)

    2 years, 9 months ago

    Hi @andreb-1,

    You can use CONCAT to combine two columns together.

    Here’s the MySQL documentation for CONCAT: CONCAT(str1,str2,…)

    Hope that helps! Let us know if you have more questions. 😊

    Thread Starter AndreB.

    (@andreb-1)

    2 years, 9 months ago

    @kimmyx

    I don’t want to combine two columns together, but rows.
    After all, CONCAT can only combine columns.

    I’ve read that it’s supposed to work with XML Path. However, I haven’t been able to do it yet.

    • This reply was modified 2 years, 9 months ago by AndreB..
    Plugin Contributor Kim L

    (@kimmyx)

    2 years, 9 months ago

    Hi @andreb-1,

    Thanks for the clarification. In that case, you can probably utilize GROUP_CONCAT() instead.

    Here are some resources:

    Let us know if that helps. 🙂

    Thread Starter AndreB.

    (@andreb-1)

    2 years, 9 months ago

    @kimmyx

    I tried it with GROUP_CONCAT (). After pressing the Execute button, however, nothing happens.
    The original code looks like this:

    SELECT	zkzCrojUwoocommerce_order_items.order_id as 'Order_id'
,		zkzCrojUwoocommerce_order_itemmeta.meta_value as 'Vorname'
FROM	zkzCrojUwoocommerce_order_items
INNER JOIN zkzCrojUwoocommerce_order_itemmeta ON zkzCrojUwoocommerce_order_itemmeta.order_item_id = zkzCrojUwoocommerce_order_items.order_item_id
WHERE	(zkzCrojUwoocommerce_order_itemmeta.meta_key = 'vorname')

    The Concat code looks like this:

    SELECT	zkzCrojUwoocommerce_order_items.order_id as 'Order_id'
,		GROUP_CONCAT(zkzCrojUwoocommerce_order_itemmeta.meta_value) as 'Vorname'
FROM	zkzCrojUwoocommerce_order_items
INNER JOIN zkzCrojUwoocommerce_order_itemmeta ON zkzCrojUwoocommerce_order_itemmeta.order_item_id = zkzCrojUwoocommerce_order_items.order_item_id
WHERE	(zkzCrojUwoocommerce_order_itemmeta.meta_key = 'vorname')
    Plugin Contributor Kim L

    (@kimmyx)

    2 years, 9 months ago

    Hi @andreb-1,

    Thanks for the update. Could you send us a message at Contact | WP Data Access?

    We might need to take a closer look.

    We’ll wait for your message!

    Thread Starter AndreB.

    (@andreb-1)

    2 years, 9 months ago

    Hi @kimmyx,

    I had an syntax error. Now the code working perfect. It works with GROUP_CONCAT.

    Thanks alot

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Sum specific rows’ is closed to new replies.