I have created a query that counts rows differently based on certain criteria (Date, isSpecial, isFirst, hour). This is the query:

WITH test AS 
(
    SELECT customer_id, performance.id, performance_id, 
           Name, Date as zDate, Date, isSpecial, isFirst, hour 
    FROM recording 
    INNER JOIN performance ON performance_id = performance.id
)

SELECT id, Date, Name, isSpecial, isFirst, hour,
    IF(NOT isSpecial AND NOT isFirst AND hour,
        (SELECT SUM(CASE WHEN NOT isFirst AND NOT isSpecial
                AND hour AND Date <= zDate
              THEN 1 ELSE 0 END) 
         FROM recording 
         INNER JOIN performance ON recording.performance_id = performance.id 
         WHERE customer_id = 1
        ), 
        IF(isSpecial AND NOT isFirst AND hour,  
            (SELECT CONCAT('P',SUM(CASE WHEN NOT isFirst
                    AND isSpecial AND hour AND Date <= zDate
                  THEN 1 ELSE 0 END)) 
             FROM recording 
             INNER JOIN performance ON recording.performance_id = performance.id 
             WHERE customer_id=1
            ),  
            IF(isFirst,'First','-')
        )
    ) AS myTest 
FROM test 
WHERE customer_id = 1 
ORDER BY Date

Results:

The query gives me the desired result for one customer_id without me having to set variables.

I have now experimented for a long time with SUM() OVER (PARTITION BY customer_id), but this always leads to "Subquery returned more than one record".

...
(SELECT CONCAT('P', SUM(CASE WHEN NOT isErst AND isProbatorik
                        AND therapiestunde AND Datum <= zDatum 
                     THEN 1 ELSE 0 END)
            OVER (PARTITION BY customer_id))
 FROM leistungserfassung 
 INNER JOIN leistung ON leistungserfassung.Leistung_id = leistung.id),
...

Is it possible to extend this summation to all customer_id's, so that for each customer_id it is always summed up again?

CodePudding user response：

This is just a guess without sufficient detail about your schema / tables:

Try this (using MySQL 8.0, MariaDB 10.6, 10.5, 10.4, etc):

Note: If you want a running SUM, just add an ORDER BY Date clause (or something similar) to the window specifications. It's possible the explicit date logic can be removed, depending on your actual requirement. It's tough to tell without a little data and expected result.

The fiddle

WITH test AS (
        SELECT customer_id, performance.id, performance_id
             , Name, Date as zDate, Date, isSpecial, isFirst, hour
          FROM recording
          JOIN performance
            ON performance_id = performance.id
     )
SELECT id, Date, Name, isSpecial, isFirst, hour
     , CASE WHEN NOT isSpecial AND NOT isFirst AND hour
            THEN SUM(CASE WHEN NOT isFirst AND NOT isSpecial AND hour AND Date <= zDate THEN 1 ELSE 0 END) OVER w
            WHEN isSpecial AND NOT isFirst AND hour
            THEN CONCAT('P', SUM(CASE WHEN NOT isFirst AND isSpecial AND hour AND Date <= zDate THEN 1 ELSE 0 END) OVER w)
            WHEN isFirst
            THEN 'First'
            ELSE '-'
        END AS myTest
  FROM test
WINDOW w AS (PARTITION BY customer_id)
 ORDER BY customer_id, Date
;

add running SUM (by Date) behavior to the window function and remove the explicit Date <= zDate logic:

WITH test AS (
        SELECT customer_id, performance.id, performance_id
             , Name, Date, isSpecial, isFirst, hour
          FROM recording
          JOIN performance
            ON performance_id = performance.id
     )
SELECT id, Date, Name, isSpecial, isFirst, hour
     , CASE WHEN NOT isSpecial AND NOT isFirst AND hour
            THEN SUM(CASE WHEN NOT isFirst AND NOT isSpecial AND hour THEN 1 ELSE 0 END) OVER w
            WHEN isSpecial AND NOT isFirst AND hour
            THEN CONCAT('P', SUM(CASE WHEN NOT isFirst AND isSpecial AND hour THEN 1 ELSE 0 END) OVER w)
            WHEN isFirst
            THEN 'First'
            ELSE '-'
        END AS myTest
  FROM test
WINDOW w AS (PARTITION BY customer_id ORDER BY Date)
 ORDER BY customer_id, Date
;

The result (with no data):

The setup (I'm sure these tables are not correct. The question did not include this detail):

CREATE TABLE recording   ( id int, performance_id int, customer_id int );

CREATE TABLE performance (
   id int, Name VARCHAR(20), Date date
 , isSpecial int, isFirst int, hour int
);