Do we have a workaround to use alias with 'where' in sql

Sales :

enter image description here

Q1) Return the name of the agent who had the highest increase in sales compared to the previous year

A) Initially I wrote the following query

Select name, (sales_2018-sales_2017) as increase 

from sales 

where increase= (select max(sales_2018-sales_2017) 

                 from sales)

I got an error saying I cannot use increase with the keyword where because "increase" is not a column but an alias

So I changed the query to the following :

Select name, (sales_2018-sales_2017) as increase 

from sales 

where (sales_2018-sales_2017)= (select max(sales_2018-sales_2017) 

                                from sales)

This query did work, but I feel there should be a better to write this queryi.e instead of writing where (sales_2018-sales_2017)= (select max(sales_2018-sales_2017) from sales). So I was wondering if there is a work around to using alias with where.

Q2) suppose the table is as following, and we are asked to return the EmpId, name who got rating A for consecutive 3 years :

enter image description here

I wrote the following query its working :

select id,name 

from ratings 

where rating_2017='A' and rating_2018='A' and rating_2019='A'

Chaining 3 columns (ratings_2017,rating_2018,rating_2019) with AND is easy, I want know if there is a better way to chain columns with AND when say we want to find a employee who has rating 'A' fro 10 consective years.

Q3) Last but not the least, I'm really interested in learning to write intermediate-complex SQL queries and take my sql skills to next level. Is there a website out there that can help me in this regard ?

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

3

The answer to the first two questions is to normalize your data.

– Salman A
Jan 1 at 9:08

1

Your main issue is that you have a bad db design, as it is now you will have to add a new column each year to your table. Instead ratings should be in its own table with columns (year, rating, sales_id). This would certainly fix the issue you have with your first two questions. The third question is off-topic for SO and in the future only ask one question at a time.

– Joakim Danielson
Jan 1 at 9:09

Please don't ask multiple questions in a single questions (especially not if some of those questions are off-topic)..

– Mark Rotteveel
Jan 1 at 11:02

add a comment |

Sales :

enter image description here

Q1) Return the name of the agent who had the highest increase in sales compared to the previous year

A) Initially I wrote the following query

Select name, (sales_2018-sales_2017) as increase 

from sales 

where increase= (select max(sales_2018-sales_2017) 

                 from sales)

I got an error saying I cannot use increase with the keyword where because "increase" is not a column but an alias

So I changed the query to the following :

Select name, (sales_2018-sales_2017) as increase 

from sales 

where (sales_2018-sales_2017)= (select max(sales_2018-sales_2017) 

                                from sales)

Q2) suppose the table is as following, and we are asked to return the EmpId, name who got rating A for consecutive 3 years :

enter image description here

I wrote the following query its working :

select id,name 

from ratings 

where rating_2017='A' and rating_2018='A' and rating_2019='A'

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

3

The answer to the first two questions is to normalize your data.

– Salman A
Jan 1 at 9:08

1

Your main issue is that you have a bad db design, as it is now you will have to add a new column each year to your table. Instead ratings should be in its own table with columns (year, rating, sales_id). This would certainly fix the issue you have with your first two questions. The third question is off-topic for SO and in the future only ask one question at a time.

– Joakim Danielson
Jan 1 at 9:09

Please don't ask multiple questions in a single questions (especially not if some of those questions are off-topic)..

– Mark Rotteveel
Jan 1 at 11:02

add a comment |

Sales :

enter image description here

Q1) Return the name of the agent who had the highest increase in sales compared to the previous year

A) Initially I wrote the following query

Select name, (sales_2018-sales_2017) as increase 

from sales 

where increase= (select max(sales_2018-sales_2017) 

                 from sales)

I got an error saying I cannot use increase with the keyword where because "increase" is not a column but an alias

So I changed the query to the following :

Select name, (sales_2018-sales_2017) as increase 

from sales 

where (sales_2018-sales_2017)= (select max(sales_2018-sales_2017) 

                                from sales)

Q2) suppose the table is as following, and we are asked to return the EmpId, name who got rating A for consecutive 3 years :

enter image description here

I wrote the following query its working :

select id,name 

from ratings 

where rating_2017='A' and rating_2018='A' and rating_2019='A'

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

Sales :

enter image description here

Q1) Return the name of the agent who had the highest increase in sales compared to the previous year

A) Initially I wrote the following query

Select name, (sales_2018-sales_2017) as increase 

from sales 

where increase= (select max(sales_2018-sales_2017) 

                 from sales)

I got an error saying I cannot use increase with the keyword where because "increase" is not a column but an alias

So I changed the query to the following :

Select name, (sales_2018-sales_2017) as increase 

from sales 

where (sales_2018-sales_2017)= (select max(sales_2018-sales_2017) 

                                from sales)

Q2) suppose the table is as following, and we are asked to return the EmpId, name who got rating A for consecutive 3 years :

enter image description here

I wrote the following query its working :

select id,name 

from ratings 

where rating_2017='A' and rating_2018='A' and rating_2019='A'

mysql sql

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

edited Jan 1 at 9:02

Joakim Danielson

9,6233725

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

asked Jan 1 at 8:57

Hemanth Ravavarapu

163

3

The answer to the first two questions is to normalize your data.

– Salman A
Jan 1 at 9:08

1

Your main issue is that you have a bad db design, as it is now you will have to add a new column each year to your table. Instead ratings should be in its own table with columns (year, rating, sales_id). This would certainly fix the issue you have with your first two questions. The third question is off-topic for SO and in the future only ask one question at a time.

– Joakim Danielson
Jan 1 at 9:09

Please don't ask multiple questions in a single questions (especially not if some of those questions are off-topic)..

– Mark Rotteveel
Jan 1 at 11:02

add a comment |

3

The answer to the first two questions is to normalize your data.

– Salman A
Jan 1 at 9:08

1

Your main issue is that you have a bad db design, as it is now you will have to add a new column each year to your table. Instead ratings should be in its own table with columns (year, rating, sales_id). This would certainly fix the issue you have with your first two questions. The third question is off-topic for SO and in the future only ask one question at a time.

– Joakim Danielson
Jan 1 at 9:09

Please don't ask multiple questions in a single questions (especially not if some of those questions are off-topic)..

– Mark Rotteveel
Jan 1 at 11:02

The answer to the first two questions is to normalize your data.

– Salman A
Jan 1 at 9:08

Your main issue is that you have a bad db design, as it is now you will have to add a new column each year to your table. Instead ratings should be in its own table with columns (year, rating, sales_id). This would certainly fix the issue you have with your first two questions. The third question is off-topic for SO and in the future only ask one question at a time.

– Joakim Danielson
Jan 1 at 9:09

Please don't ask multiple questions in a single questions (especially not if some of those questions are off-topic)..

– Mark Rotteveel
Jan 1 at 11:02

add a comment |

2 Answers
2

active

oldest

votes

1) You are referencing an expression with a table column value, and therefore you would need to define the expression first(either using an inline view/cte for increase). After that you can refer it in the query
Eg:

select * 

  from ( select name, (sales_2018-sales_2017) as increase 

           from sales 

       )x

 where x.increase= (select max(sales_2018-sales_2017) 

                      from sales)

Another option would be to use analytical functions for getting your desired results, if you are in mysql 8.0

   select * 

     from ( select name

                  ,(sales_2018-sales_2017) as increase 

                  ,max(sales_2018-sales_2017) over(partition by (select null)) as max_increase

              from sales 

           )x

     where x.increase=x.max_increase

Q2) There are alternative ways to write this. But the basic issue is with the table design where you are storing each rating year as a new column. Had it been a row it would have been more easy.

Here is another way

select id,name 

  from ratings 

 where length(concat(rating_2017,rating_2018,rating_2019))-

       length(replace(concat(rating_2017,rating_2018,rating_2019)),'A','')=3

Q3) Check out some example of problems from hackerrank or https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/. You can also search for the questions and answers from stackoverflow to get solutions to tough problems people faced

answered Jan 1 at 10:05

George Joseph

1,59059

add a comment |

Q1 : you can simply order and limit the query results (hence no subquery is necessary) ; also, column aliases are allowed in the ORDER BY clause

SELECT 

    name,

    sales_2018-sales_2017 as increase 

FROM sales 

ORDER BY increase DESC

LIMIT 1

Q2 : your query is fine ; other options exists, but they will not make it faster or easier to maintain.

Finally, please note that your best option overall would be to modify your database layout : you want to have yearly data in rows, not in columns ; there should be only one column to store the year instead of several. That would make your queries simpler to write and to maintain (and you wouldn’t need to create a new column every new year...)

answered Jan 1 at 10:59

GMB

16.5k3927

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994190%2fdo-we-have-a-workaround-to-use-alias-with-where-in-sql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

select * 

  from ( select name, (sales_2018-sales_2017) as increase 

           from sales 

       )x

 where x.increase= (select max(sales_2018-sales_2017) 

                      from sales)

Another option would be to use analytical functions for getting your desired results, if you are in mysql 8.0

   select * 

     from ( select name

                  ,(sales_2018-sales_2017) as increase 

                  ,max(sales_2018-sales_2017) over(partition by (select null)) as max_increase

              from sales 

           )x

     where x.increase=x.max_increase

Q2) There are alternative ways to write this. But the basic issue is with the table design where you are storing each rating year as a new column. Had it been a row it would have been more easy.

Here is another way

select id,name 

  from ratings 

 where length(concat(rating_2017,rating_2018,rating_2019))-

       length(replace(concat(rating_2017,rating_2018,rating_2019)),'A','')=3

answered Jan 1 at 10:05

George Joseph

1,59059

add a comment |

select * 

  from ( select name, (sales_2018-sales_2017) as increase 

           from sales 

       )x

 where x.increase= (select max(sales_2018-sales_2017) 

                      from sales)

Another option would be to use analytical functions for getting your desired results, if you are in mysql 8.0

   select * 

     from ( select name

                  ,(sales_2018-sales_2017) as increase 

                  ,max(sales_2018-sales_2017) over(partition by (select null)) as max_increase

              from sales 

           )x

     where x.increase=x.max_increase

Q2) There are alternative ways to write this. But the basic issue is with the table design where you are storing each rating year as a new column. Had it been a row it would have been more easy.

Here is another way

select id,name 

  from ratings 

 where length(concat(rating_2017,rating_2018,rating_2019))-

       length(replace(concat(rating_2017,rating_2018,rating_2019)),'A','')=3

answered Jan 1 at 10:05

George Joseph

1,59059

add a comment |

select * 

  from ( select name, (sales_2018-sales_2017) as increase 

           from sales 

       )x

 where x.increase= (select max(sales_2018-sales_2017) 

                      from sales)

Another option would be to use analytical functions for getting your desired results, if you are in mysql 8.0

   select * 

     from ( select name

                  ,(sales_2018-sales_2017) as increase 

                  ,max(sales_2018-sales_2017) over(partition by (select null)) as max_increase

              from sales 

           )x

     where x.increase=x.max_increase

Q2) There are alternative ways to write this. But the basic issue is with the table design where you are storing each rating year as a new column. Had it been a row it would have been more easy.

Here is another way

select id,name 

  from ratings 

 where length(concat(rating_2017,rating_2018,rating_2019))-

       length(replace(concat(rating_2017,rating_2018,rating_2019)),'A','')=3

answered Jan 1 at 10:05

George Joseph

1,59059

select * 

  from ( select name, (sales_2018-sales_2017) as increase 

           from sales 

       )x

 where x.increase= (select max(sales_2018-sales_2017) 

                      from sales)

Another option would be to use analytical functions for getting your desired results, if you are in mysql 8.0

   select * 

     from ( select name

                  ,(sales_2018-sales_2017) as increase 

                  ,max(sales_2018-sales_2017) over(partition by (select null)) as max_increase

              from sales 

           )x

     where x.increase=x.max_increase

Q2) There are alternative ways to write this. But the basic issue is with the table design where you are storing each rating year as a new column. Had it been a row it would have been more easy.

Here is another way

select id,name 

  from ratings 

 where length(concat(rating_2017,rating_2018,rating_2019))-

       length(replace(concat(rating_2017,rating_2018,rating_2019)),'A','')=3

answered Jan 1 at 10:05

George Joseph

1,59059

answered Jan 1 at 10:05

George Joseph

1,59059

answered Jan 1 at 10:05

George Joseph

1,59059

answered Jan 1 at 10:05

George Joseph

1,59059

add a comment |

Q1 : you can simply order and limit the query results (hence no subquery is necessary) ; also, column aliases are allowed in the ORDER BY clause

SELECT 

    name,

    sales_2018-sales_2017 as increase 

FROM sales 

ORDER BY increase DESC

LIMIT 1

Q2 : your query is fine ; other options exists, but they will not make it faster or easier to maintain.

answered Jan 1 at 10:59

GMB

16.5k3927

add a comment |

Q1 : you can simply order and limit the query results (hence no subquery is necessary) ; also, column aliases are allowed in the ORDER BY clause

SELECT 

    name,

    sales_2018-sales_2017 as increase 

FROM sales 

ORDER BY increase DESC

LIMIT 1

Q2 : your query is fine ; other options exists, but they will not make it faster or easier to maintain.

answered Jan 1 at 10:59

GMB

16.5k3927

add a comment |

Q1 : you can simply order and limit the query results (hence no subquery is necessary) ; also, column aliases are allowed in the ORDER BY clause

SELECT 

    name,

    sales_2018-sales_2017 as increase 

FROM sales 

ORDER BY increase DESC

LIMIT 1

Q2 : your query is fine ; other options exists, but they will not make it faster or easier to maintain.

answered Jan 1 at 10:59

GMB

16.5k3927

Q1 : you can simply order and limit the query results (hence no subquery is necessary) ; also, column aliases are allowed in the ORDER BY clause

SELECT 

    name,

    sales_2018-sales_2017 as increase 

FROM sales 

ORDER BY increase DESC

LIMIT 1

Q2 : your query is fine ; other options exists, but they will not make it faster or easier to maintain.

answered Jan 1 at 10:59

GMB

16.5k3927

answered Jan 1 at 10:59

GMB

16.5k3927

answered Jan 1 at 10:59

GMB

16.5k3927

answered Jan 1 at 10:59

GMB

16.5k3927

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu