CODEWITHSUNDEEP
mysqlsql
Pittor
Pittor

Reputation: 129

SUM() the results of GROUP BY column

Table 'volcado': id, reserva, cantidad

With the following query:

SELECT (CASE
         WHEN sum(cantidad) > 0 THEN 1
         WHEN sum(cantidad) <= 0 THEN 0
       END) AS suma 
FROM volcado
GROUP BY reserva

The result is:

╔══════╗
║ suma ║
╠══════╣
║  1   ║
║  1   ║
║  0   ║
║  1   ║
║  0   ║
╚══════╝

I need the result of the query would be: 3

How can I SUM() the rows of 'suma' on the same query?

I don't get it run with:

SELECT SUM(SELECT (CASE
        WHEN sum(cantidad) > 0 THEN 1
        WHEN sum(cantidad) <= 0 THEN 0
      END) AS suma FROM volcado GROUP BY reserva)
FROM volcado

Error:

Subquery returns more than 1 row

Thanks in advance.

Upvotes: 1

Views: 1480

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can simplify the query, if you like. So, instead of using case use having:

SELECT COUNT(*)
FROM (SELECT reserva
      FROM volcado
      GROUP BY reserva
      HAVING SUM(cantidad) > 0
     ) r;

Upvotes: 0

Shinchan_Shiro
Shinchan_Shiro

Reputation: 93

You can use CTE:

;WITH cte AS
  (SELECT CASE
              WHEN sum(cantidad) > 0 THEN 1
              WHEN sum(cantidad) <= 0 THEN 0
          END suma
   FROM volcado
   GROUP BY reserva)
SELECT sum(suma)
FROM cte

Upvotes: 0

Kateract
Kateract

Reputation: 852

This should work, use the subquery as a table instead of as a column.

SELECT SUM(suma) from 
    (SELECT (CASE
        WHEN sum(cantidad) > 0 THEN 1
        WHEN sum(cantidad) <= 0 THEN 0
        END) AS suma FROM volcado GROUP BY reserva)

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this way

SELECT SUM(a.suma) from 
    (SELECT (CASE
        WHEN sum(cantidad) > 0 THEN 1
        WHEN sum(cantidad) <= 0 THEN 0
        END) AS suma FROM volcado GROUP BY reserva)a

Upvotes: 3

Related Questions