Search code examples
mysqlsqlrowunionsqldatatypes

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

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

Solution

  • 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.