CODEWITHSUNDEEP
javamysqlsql
JB P
JB P

Reputation: 96

How can I get SUM result as an alias in MYSQL?

    SELECT
        date_format(LEFT(CAST(REQDT AS UNSIGNED),8),'%Y-%m-%d') AS REQDT,
        count(SVCE_DOMAIN) COUNTSVCE


        FROM BC_HISTORY
        where reqid ='MSYS'
        
        group by date_format(LEFT(CAST(REQDT AS UNSIGNED),8),'%Y-%m-%d');

The code I wrote above results show like this.

enter image description here

I want to know how to Sum numbers in the column 'COUNTSVCE'. The column 'COUNTSVCE' is an alias of count(SVCE_DOMAIN). Rollup function might be a good solution but I can't use it in my JAVA query so I get the result as alias. Thank you.

Upvotes: 0

Views: 280

Answers (1)

Akina
Akina

Reputation: 42728

It seems that you need either in

SELECT total.*
FROM ( SELECT DATE_FORMAT(LEFT(CAST(reqdt AS UNSIGNED),8),'%Y-%m-%d') AS reqdt,
              COUNT(svce_domain) countsvce
       FROM bc_history
       GROUP BY reqdt
     UNION ALL
       SELECT DATE_FORMAT(LEFT(CAST(reqdt AS UNSIGNED),8),'%Y-%m-%d') AS reqdt,
              COUNT(svce_domain) countsvce
       FROM bc_history
       WHERE reqid ='MSYS'
       GROUP BY reqdt
     ) AS total

or in

SELECT DATE_FORMAT(LEFT(CAST(reqdt AS UNSIGNED),8),'%Y-%m-%d') AS reqdt,
       COUNT(svce_domain) total_count,
       SUM(reqid ='MSYS') msys_count
FROM bc_history
GROUP BY reqdt

UPDATE according to altered question

Why you cannot use

SELECT DATE_FORMAT(LEFT(CAST(reqdt AS UNSIGNED),8),'%Y-%m-%d') AS reqdt,
       COUNT(svce_domain) countsvce
FROM bc_history
WHERE reqid ='MSYS'
GROUP BY reqdt WITH ROLLUP;

?

Total sum for a column will be the last row, with NULL value in reqdt column (which may be replaced with some literal, for example, 'Total').

I want to use this query in Mybatis in Springboot. But my query send data only as alias name. So I need result as alias.

I understand nothing. But you may convert the query to the subquery.

SELECT *
FROM (
SELECT DATE_FORMAT(LEFT(CAST(reqdt AS UNSIGNED),8),'%Y-%m-%d') AS reqdt,
       COUNT(svce_domain) countsvce
FROM bc_history
WHERE reqid ='MSYS'
GROUP BY reqdt WITH ROLLUP
) AS subquery
ORDER BY reqdt IS NULL; -- place total row last

Now assign the alias by the common way, like this is a table, not a subquery.

Upvotes: 3

Related Questions