CODEWITHSUNDEEP
mysqlsqlsumcase
Fakerhardcore
Fakerhardcore

Reputation: 13

MySQL SUM function and CASE statement

SELECT 
    CASE schuleArtID 
        WHEN 1 THEN SUM(schuleSummeSchueler) / 8
        WHEN 2 THEN SUM(schuleSummeSchueler) / 6 * 0.8
        WHEN 3 THEN SUM(schuleSummeSchueler) / 6 * 0.8
        WHEN 4 THEN SUM(schuleSummeSchueler) / 5 * 0.8
        WHEN 5 THEN SUM(schuleSummeSchueler) / 3 * 0.5
        WHEN 6 THEN SUM(schuleSummeSchueler) / 3 * 0.8
    END AS potenzial
FROM 
    sys_theorie_schule
WHERE
    filialID = '311'
GROUP BY
    schuleArtID

This works, but now I need a sum because there are multiple rows.

SELECT 
    SUM(
        CASE schuleArtID 
            WHEN 1 THEN SUM(schuleSummeSchueler) / 8
            WHEN 2 THEN SUM(schuleSummeSchueler) / 6 * 0.8
            WHEN 3 THEN SUM(schuleSummeSchueler) / 6 * 0.8
            WHEN 4 THEN SUM(schuleSummeSchueler) / 5 * 0.8
            WHEN 5 THEN SUM(schuleSummeSchueler) / 3 * 0.5
            WHEN 6 THEN SUM(schuleSummeSchueler) / 3 * 0.8
        END
    ) AS potenzial
FROM 
    sys_theorie_schule
WHERE
    filialID = '311'
GROUP BY
    schuleArtID

Here, I only get the result [Err] 1111 - Invalid use of group function.

Here is the structure of the table (Sorry for the German column names):

CREATE TABLE `sys_theorie_schule` (
  `schuleID` int(11) NOT NULL AUTO_INCREMENT,
  `filialID` int(11) NOT NULL,
  `schuleName` varchar(255) NOT NULL,
  `schuleStrasse` varchar(255) NOT NULL,
  `schulePlz` varchar(8) NOT NULL,
  `schuleOrt` varchar(255) NOT NULL,
  `schuleArtID` int(11) NOT NULL,
  `schuleSummeSchueler` int(11) NOT NULL,
  `schuleEntfernung` int(11) NOT NULL,
  `online` tinyint(1) NOT NULL DEFAULT '1',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`schuleID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

MySQL-Version: 5.0.51a-24+lenny5

Could somebody help, please? Many thanks!

Upvotes: 1

Views: 9306

Answers (1)

user359040
user359040

Reputation:

Try:

SELECT 
    SUM(
        CASE schuleArtID 
            WHEN 1 THEN schuleSummeSchueler / 8
            WHEN 2 THEN schuleSummeSchueler / 6 * 0.8
            WHEN 3 THEN schuleSummeSchueler / 6 * 0.8
            WHEN 4 THEN schuleSummeSchueler / 5 * 0.8
            WHEN 5 THEN schuleSummeSchueler / 3 * 0.5
            WHEN 6 THEN schuleSummeSchueler / 3 * 0.8
        END
    ) AS potenzial
FROM 
    sys_theorie_schule
WHERE
    filialID = '311'

You can't sum an expression that already includes a sum, and there is no point in grouping by schuleArtID when you want a grand total.

Upvotes: 4

Related Questions