CODEWITHSUNDEEP
mysql
Richard Mayhew
Richard Mayhew

Reputation: 1

How do i write this query

Here is the problem

Using the CASE expression, display the author’s first name concatenated to his last name separated by a space concatenated to a “ is: “. Concatenate the previous result to a slogan based on the value in the state column. If state is “CA” display “Sunny”. If state is “CO”, display “a rarity”. If state is “FL”, display “a peach”. If state is “NY”, display “likes apple pie”. Name the column “Author Characteristics”. Order the result by “Author Characteristics”.

Here is the code I have written so far

select concat(au_fname, au_lname, ' is: '),
case state
    when 'CA'
        then 'Sunny'
    when 'CO'
        then 'a rarity'
    when 'FL'
        then 'a peach'
    when 'NY'
        then 'like apple pie'
    else 'state'
end
AS "Author Characteristics"
FROM authors
ORDER BY 1;

The problem i am having is the output is giving me two columns, one titled from the concat statement and the second column is titled Author Characteristics. I need to get just one field so the information is all together with only one title.

Upvotes: 0

Views: 83

Answers (4)

John Woo
John Woo

Reputation: 263713

In order to have one column in the result list, you also need to include the CASE statement inside the CONCAT() function. But in ORDER BY clause, you need again to have the CASE statement since you want to order them by state. Orderinf the records by ORDER BY 1 will sort the records on au_fname.

select  concat(au_fname, ' ',au_lname, ' is: ', 
                case state
                    when 'CA' then 'Sunny'
                    when 'CO' then 'a rarity'
                    when 'FL' then 'a peach'
                    when 'NY' then 'like apple pie'
                    else 'state'
                end) `Author Characteristics`
FROM authors
ORDER BY case state
            when 'CA' then 'Sunny'
            when 'CO' then 'a rarity'
            when 'FL' then 'a peach'
            when 'NY' then 'like apple pie'
            else 'state' end

but if I misunderstood the question, you can directly order them via the ALIAS,

ORDER BY `Author Characteristics`

Upvotes: 1

No'am Newman
No'am Newman

Reputation: 6477

Include the 'case' statement within the 'concat' function

select concat(au_fname, au_lname, ' is: ', 
case state
when 'CA'
    then 'Sunny'
when 'CO'
    then 'a rarity'
when 'FL'
    then 'a peach'
when 'NY'
    then 'like apple pie'
else 'state'
end)
AS "Author Characteristics"
FROM authors
ORDER BY 1;

Upvotes: 1

Ryan
Ryan

Reputation: 28187

Try 

select concat(au_fname, au_lname, ' is: ',
case state
    when 'CA'
        then 'Sunny'
    when 'CO'
        then 'a rarity'
    when 'FL'
        then 'a peach'
    when 'NY'
        then 'like apple pie'
    else 'state'
end )
AS "Author Characteristics"
FROM authors
ORDER BY 1;

This will include the 'state' values in your concat statement. In your original code, you ended up with two columns because the concat statement resulted in one column, and the case in another.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

Just make the case another argument to CONCAT.

select concat(au_first, au_last, ' is: ', case state
when 'CA' then 'Sunny' when 'CO' then 'a rarity' when 'FL' then 'a peach'
when 'NY' then 'like apple pie' else 'state' end) AS "Author Characteristics"

Upvotes: 2

Related Questions