Thanks so much for the support, I will return don't worry, I'm hoping early next year, I'm just trying to get some things in order so I can post regularly.
Hi solution and explanation are awesome, need more case with respect to system time concat statement and many more thanks in advance but it's required for further improvement in this case statement.
Why we need to give columns in Group by clause which are using case statements? Select c1, c2='x' sum(values in c3) sum(values in c4) end as 'z' from table A where c5='Y' and c6='w' group by c1, ( Do we need to use c2 here ) c1, Z column (I need Z values grouped by only c1 column )
I think you're asking why we need to use the Customers table, that's because the discount depends on the CustomerType. If you can clarify the question a bit more, I can help.
On mobile so I cannot elaborate, but this feels like it could benefit from a stored procedure to calculate the discount. I'm thinking about calling a proc in the case statement and feed it the parameters for the different discounts. WHEN CustomerType THEN EXEC dbo.CalculateDiscount(...) END AS Discount
Interesting idea, as it's just a simple select I would suggest a table-valued function and storing the discount values in another table and joining to it.
In my case statement they provide the conditions for the columns but we need to fetch the values from that column. E.g. CASE When ABC.(Tbl n).Contact desc= 'phonenumber'. I want here phonenumbers for different IDs. How to do this??
There are a couple of solutions I have in mind but it would depend on the setup, if you have a situation where you have contacts and they have different contact numbers but a preferred in another column then it can be as straight forward as CASE prefferedcontactnumber WHEN 'Home' THEN homecontactnumber WHEN 'Mobile' THEN mobilecontactnumber etc. This will return the preferred contact number in a column, the other scenario you might be describing is a generic table where you have an ID, attribute and a value, in that scenario pivoting the data would probably be easier.
Hi, in all your videos you talk about relational set which is basis for relational databases. So in order completely understand the relational set which math should I learn, so that I completely understand the logic behind any given SQL query? Please advice.
Hi Sri, thanks for the comment, it's an interesting question. One thing I would say is that you don't need to fully understand the math behind it, if you have a basic understanding of sets and predicates it will serve you well when writing SQL. However if you would like to look into then the two branches are set theory and predicate logic. Edgar F Codd has written some books on relational databases such as the relational model for database management that you might like to read.
Can you think of a more elegant solution?
Well done on hitting 1 million total views Bearded Dev. Your channel is pure gold and I wish you were still putting up content
Thanks so much for the support, I will return don't worry, I'm hoping early next year, I'm just trying to get some things in order so I can post regularly.
This was very helpful! Thank you.
Hi solution and explanation are awesome, need more case with respect to system time concat statement and many more thanks in advance but it's required for further improvement in this case statement.
Why we need to give columns in Group by clause which are using case statements?
Select
c1,
c2='x' sum(values in c3) sum(values in c4) end as 'z'
from table A
where c5='Y' and c6='w'
group by c1, ( Do we need to use c2 here )
c1, Z column (I need Z values grouped by only c1 column )
I think you're asking why we need to use the Customers table, that's because the discount depends on the CustomerType. If you can clarify the question a bit more, I can help.
Great job with real world problem
Thank you so much bro
On mobile so I cannot elaborate, but this feels like it could benefit from a stored procedure to calculate the discount. I'm thinking about calling a proc in the case statement and feed it the parameters for the different discounts.
WHEN CustomerType
THEN EXEC dbo.CalculateDiscount(...)
END AS Discount
Interesting idea, as it's just a simple select I would suggest a table-valued function and storing the discount values in another table and joining to it.
a function is also useable to calculate the discount.
In my case statement they provide the conditions for the columns but we need to fetch the values from that column.
E.g. CASE When ABC.(Tbl n).Contact desc= 'phonenumber'.
I want here phonenumbers for different IDs.
How to do this??
There are a couple of solutions I have in mind but it would depend on the setup, if you have a situation where you have contacts and they have different contact numbers but a preferred in another column then it can be as straight forward as CASE prefferedcontactnumber WHEN 'Home' THEN homecontactnumber WHEN 'Mobile' THEN mobilecontactnumber etc. This will return the preferred contact number in a column, the other scenario you might be describing is a generic table where you have an ID, attribute and a value, in that scenario pivoting the data would probably be easier.
Hi, in all your videos you talk about relational set which is basis for relational databases. So in order completely understand the relational set which math should I learn, so that I completely understand the logic behind any given SQL query? Please advice.
Hi Sri, thanks for the comment, it's an interesting question. One thing I would say is that you don't need to fully understand the math behind it, if you have a basic understanding of sets and predicates it will serve you well when writing SQL. However if you would like to look into then the two branches are set theory and predicate logic. Edgar F Codd has written some books on relational databases such as the relational model for database management that you might like to read.
good video, but not able to clearly hear.