Search code examples
sqlmariadbheidisql

Unexpected values (values being added) to column/output generated/formatted incorrectly from SQL

CREATE TABLE `flatpack_apn` (
    `FlatpackID` INT(11) NOT NULL AUTO_INCREMENT,
    `Name` TINYTEXT NOT NULL,
    `Colour` TEXT NULL DEFAULT NULL,
    `Type` ENUM('Office','Kitchen','Bedroom','General') NOT NULL,
    `UnitPrice` DECIMAL(5,2) NULL DEFAULT NULL,
    PRIMARY KEY (`FlatpackID`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
AUTO_INCREMENT=12
;

Above is the table I have.

I am asked to:

List flatpack details in the format: FlatpackName (Type)-UnitPrice with heading “Flatpack”.

To do so, I used:

SELECT CONCAT ('FlatpackName'+ '('+Type+ ')' +'-'+UnitPrice) 
FROM coursework.flatpack_apn;

However, despite the fact that the query runs without errors, the only output I get is:

76
76
16
32.2
48.2
68.2
129
26
26
26
226

Here is a picture of the sample data I have populated my table with.

https://imgur.com/a/QYFqsL1

So it would appear then that my query has returned only the unitprices, and then added to the existing values (I don't know why this has occurred).

How can I ensure that I am able to format and output the data properly?

Solution

  • You are using + on strings . .. it doesn't make sense to add them. When you do so, leading digits are turned into a number, so 'FlatpackName' becomes 0.

    You want commas instead:

    SELECT CONCAT('FlatpackName', '(', Type,  ')', '-', UnitPrice)