CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5471

Get total of column and row in SQL result

I am using the following SQL statement to get logistics costs from the database:

SELECT
country,
sum(Costs_Inbound), sum(Costs_Outbound)
FROM Logistics
GROUP BY country

The database (sqlfiddle) can be found here. All this works fine so far.

Now, I want that in the results the total of the columns and rows is also diplayed. Therefore I tried to go with the following solutions from here:

SELECT country, Costs_Inbound, Costs_Outbound
FROM Logistics
UNION ALL
SELECT null,sum(country),null,sum(Costs_Inbound),null,sum(Costs_Outbound)
FROM Logistics

Unfortunately, I could not make it work.

Do you know where there is the mistake in my code?

Upvotes: 1

Views: 99

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Your code doesn't work because the first subquery has no group by and the two queries have different numbers of columns.

Use ROLLUP:

SELECT country, sum(Costs_Inbound), sum(Costs_Outbound),
       (sum(Costs_Inbound) + sum(Costs_Outbound)) as in_plus_out
FROM Logistics
GROUP BY country WITH ROLLUP;

If you want the values in the same row, then use a JOIN:

SELECT l.country, sum(l.Costs_Inbound), total.total_in, 
       sum(l.Costs_Outbound), total.total_out
FROM Logistics l CROSS JOIN
     (SELECT sum(l.Costs_Inbound) as total_in, sum(l.Costs_Outbound) as total_out
      FROM logistics l
     ) total
GROUP BY l.country, total.total_in, total.total_out;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try this below query: for union/union all the column no should be equal for selection, in your query your columns no are not same

SELECT country, Costs_Inbound, Costs_Outbound
FROM Logistics
UNION ALL
SELECT null,sum(Costs_Inbound),sum(Costs_Outbound)
FROM Logistics

http://sqlfiddle.com/#!9/2c9fa44/17

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want :

SELECT country, Costs_Inbound, Costs_Outbound
FROM Logistics
UNION ALL
SELECT NULL, SUM(Costs_Inbound), SUM(Costs_Outbound)
FROM Logistics;

Upvotes: 1

Related Questions