Inserting text into SQL SELECT query row

Question

Sorry if the question has already been asked. I couldn't think of the words for what I was trying to do but I have done some Google foo before asking.

In the code below I'm trying to UNION onto the select query a Summary line. In the first Column of the Summary row I wanted the text Summary. I'm pretty sure I have done it this way before in the past but for the life of me can't get it to work as postgres just thinks it's an invalid int.

Any help would be greatly appreciated.

CREATE OR REPLACE VIEW OrderHistory
AS SELECT OrderLine.ShopOrderID, Book.Title, OrderLine.UnitSellingPrice, OrderLine.Quantity,  
SUM(Quantity*UnitSellingPrice) AS total
FROM OrderLine
INNER JOIN Book
ON OrderLine.BookID = Book.BookID
WHERE OrderLine.BookID = 2
GROUP BY OrderLine.ShopOrderID, Book.Title, OrderLine.Quantity, OrderLine.UnitSellingPrice;

SELECT * FROM OrderHistory

UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice) 
FROM OrderHistory
ORDER BY total;

If this code it terrible as it is I apologies as I'm a bit of an SQL noob trying to learn at the moment so there could be a million and one ways of doing this more efficiently.

EDIT: Sorry. Should probably also have pointed out it's this line I am having trouble with :)

UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice)

What data type is ShopOrderId? You might need to move 'Summary' to the second column Title. — Ramoth
– Ramoth, Commented Apr 18, 2014 at 21:10

Gordon Linoff · Accepted Answer · 2014-04-18 21:12:20Z

1

Try this:

CREATE OR REPLACE VIEW OrderHistory
AS SELECT cast(OrderLine.ShopOrderID as varchar(255)) as ShopOrderId, Book.Title, OrderLine.UnitSellingPrice, OrderLine.Quantity,  
SUM(Quantity*UnitSellingPrice) AS total
FROM OrderLine
INNER JOIN Book
ON OrderLine.BookID = Book.BookID
WHERE OrderLine.BookID = 2
GROUP BY OrderLine.ShopOrderID, Book.Title, OrderLine.Quantity, OrderLine.UnitSellingPrice;

SELECT * FROM OrderHistory

UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice) 
FROM OrderHistory
ORDER BY total;

That is, cast the ShopOrderId into a string.

answered Apr 18, 2014 at 21:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RyanH Over a year ago

Thanks for this. Guy below explained why I was wrong which is good as I need to learn why what needs to be what. This workaround will work for now thou thanks! :)

Gordon Linoff Over a year ago

When I read @Frazz's explanation, I was surprised it didn't include this solution.

RyanH Over a year ago

Well if I have learnt anything from using stackoverflow it's that people like to get you to come to the conclusion yourself if possible. Which for the most part I agree with as you don't learn otherwise I guess. Hence why I Google the hell out of a question before I bother posting. Anyway thanks again! :)

Frazz · Accepted Answer · 2014-04-18 21:09:19Z

1

And Postgres is probably right.

When you execute a UNION the column structure of what you are trying to unite must match. 'Summary' is certainly a char/varchar. I suspect OrderLine.ShopOrderID is an integer. There is no way you can use one same column to contain both type of data without typecasting. And the only typecast I see as possible is OrderLine.ShopOrderID to char/varchar... a bit ugly, but possible.

Another solution is to add a char/varchar column in front of these... put null or empty string or some text constant in the first select, and 'Summary' in the second. Then in second select you must also put NULL in the ShopOrderID column... just like you are already doing with Book.Title and OrderLine.UnitSellingPrice

answered Apr 18, 2014 at 21:09

Frazz

3,0432 gold badges22 silver badges33 bronze badges

2 Comments

RyanH Over a year ago

Thanks for the explanation. Looking now on old code I can see that I indeed using a varchar where I had done this in the past instead of an int. I will use the workaround above to cast the int into a varchar but it's nice to know why it didn't work :).

Frazz Over a year ago

putting together details and summary is never a beautiful thing. This usually serves to build a report or a table. I rather execute two distinct queries and have the application layout the data correctly. The server is executing two queries anyway :)

Collectives™ on Stack Overflow

Inserting text into SQL SELECT query row

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related