SQL Select Case SUM()/total?-CodePudding

I'm trying to produce a row for the total numbers of tracks in a database, currently I've got one row for one type of media and one row for another type, I just need to produce a total row for this but I can't figure it out.

I tried the below code in hopes it would work, but it comes up with the error about aggregate functions are not allowed in the GROUP BY clause

    SELECT CASE
    WHEN m.name LIKE '% AAC %' OR m.name LIKE 'AAC %' THEN 'AAC'
    WHEN m.name NOT LIKE '% AAC %' OR m.name NOT LIKE 'AAC %' THEN 'non-AAC'
    WHEN COUNT(m.name) <> 0 THEN 'Total'
    END AS Media, COUNT(t.MediaTypeID) AS 'Tracks'
    FROM MediaType m, Track t
    WHERE m.MediaTypeID = t.MediaTypeID
    GROUP BY Media

Where am I going wrong? This is done in SQLite

CodePudding user response：

Use this:

 SELECT CASE
WHEN m.name LIKE '% AAC %' OR m.name LIKE 'AAC %' THEN 'AAC'
WHEN m.name NOT LIKE '% AAC %' OR m.name NOT LIKE 'AAC %' THEN 'non-AAC'
WHEN COUNT(m.name) <> 0 THEN 'Total'
END AS Media, COUNT(t.MediaTypeID) AS 'Tracks'
FROM MediaType m, Track t
WHERE m.MediaTypeID = t.MediaTypeID
GROUP BY m.name,t.MediaTypeID

In this if using any aggregate function than the column name must in group by Other wise gives error

CodePudding user response：

where is the 'else' statement ?