SQL: Add dash Line (-) at the end of query result

Question

Here Im trying to add extra row by using union query between data and result data.

select color, item, sum(qua) from inventory
Group by color, item
UNION
Select '----','----','----'
Union
select Count(color), 'total', sum(qua)
from inventory

Result would be:

ITME    COLOR   QUA
----    -----   ----
chair   Black   520
chair   pink    1028
chair   Red 1050
chair   Yellow  524
table   Black   1048
table   Blue    124
table   pink    624
table   Red 524
table   Yellow  548
-----   -----   -----    <----This extra row.
13  total   5990

I used above query but It show me Data type mismatch error. Conversion failed when converting the varchar value '----' to data type int. I'm using Microsoft SQL server management studio r2 2008

You can try convert columns QUA and ITME to varchar. – Robert Aug 31 '12 at 21:51 — Robert, Aug 31 '12 at 21:51

score 5 · Answer 1 · answered Aug 31 '12 at 21:47

5

No, you shouldn't do this. To do it you'd have to cast the value of sum(qua) to a varchar, but this is a bad idea.

Drawing a line should be handled in your presentation layer.

answered Aug 31 '12 at 21:47

Mark Byers

811,555
193
1,581
1,452

1

+1 .. and totals, if so desired, could be obtained via the [`WITH ROLLUP`](http://dev.mysql.com/doc/en/group-by-modifiers.html) modifier to the `GROUP BY` clause, without needing to use `UNION`. – eggyal Aug 31 '12 at 22:19
@MarkByers . . . Even I agree with this. And I tend to think that databases should do more work rather than less with respect to the presentation layer. – Gordon Linoff Aug 31 '12 at 22:36
I agree with eggyal, but Its works fine in query and give me the result with right total which im looking for. Thank you for your suggestion, will keep in mind for next time. – DBA_assis Sep 04 '12 at 15:38

score 1 · Accepted Answer · answered Aug 31 '12 at 22:01

Use UNION ALL instead of UNION

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

Reference

You should always be using UNION ALL unless you are removing duplicates.

codingbiz · Answer 3 · 2012-08-31T22:02:51.043

0

This should be used for testing in the query window only otherwise, follow Mark Byers advice

select color, item, CAST(sum(qua) as VARCHAR) As QUA from inventory
Group by color, item
UNION ALL
Select '----','----','----'
UNION ALL
select CAST(Count(color) AS VARCHAR), 'total', CAST(sum(qua) AS VARCHAR)
from inventory

edited Aug 31 '12 at 22:02

answered Aug 31 '12 at 21:54

codingbiz

26,179
8
59
96

1

Won't work as expected, for '----' row may as well come first in results. :] – Kuba Wyrostek Aug 31 '12 at 21:59
Gives me syntax error near "CAST(sum(qua) AS varchar)" that unexpected varchar. – DBA_assis Sep 04 '12 at 14:25
Sorry for that. I think that was SQL Server syntax and it worked. I browsed the internet and found that MySQL does not support `CAST(x AS VARCHAR)`. You can try `CAST(x AS CHAR(100)` http://stackoverflow.com/questions/1873085/how-to-convert-from-varbinary-to-char-varchar-in-mysql – codingbiz Sep 04 '12 at 15:08
Oh sorry about that. It is sql syntax, Just im checking in sql workbench which is gives me this error. its work perfectly in sql studio. Thanks for all your effort. and its works fine with original data. Appreciate. – DBA_assis Sep 04 '12 at 15:40

score 0 · Answer 4 · answered Aug 31 '12 at 21:58

There are two things here. First is that types of sum(qua) and '----' are different (number vs. text).

select color, item, convert(nvarchar(30),sum(qua)) from inventory
Group by color, item
UNION
Select '----','----','----'
Union
select Count(color), 'total', sum(qua)
from inventory

Second is that you expect SQL to return data in order of SELECTs and that is not the case. Rows from unions can come in any order. To achieve the result you want an ordering trick is neccessary:

select color, item, summary from
(
    select 0 as ToOrder, color, item, convert(nvarchar(30),sum(qua)) as summary from inventory
    Group by color, item
    UNION
    Select 1, '----','----','----'
    Union
    select 2, Count(color), 'total', sum(qua)
    from inventory
) as x
order by ToOrder asc

SQL: Add dash Line (-) at the end of query result

4 Answers4