Please note the COST column used in this table represents the total cost of purchase hence we do not need to multiple quantity with cost to get the total cost. I designed the dataset with an assumption that COST is the total purchase cost.
Hello, please help me reconstruct this using SELECT. The with clause isn’t supported WITH seq(n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < DATEDIFF(DAY, '20210101', (DATEADD(DAY, -1, DATEADD(YEAR, 30, '20210101'))))), d(d) AS (SELECT DATEADD(DAY, n, '20210101') FROM seq) SELECT d, COUNT (*) ON a.dogma_registereddate = d OR a.dogma_removeddate IS NULL) WHERE a.dogma_charitynumber != 'SC000036' GROUP BY d ORDER BY d OPTION (MAXRECURSION 0);
alot of people just show how to make the query, what I call "watch me type". You actually teach the how and whys. i learned alot with your channel. thanks
You're the best resource I have found! I've taken classes on Udemy and everything but it's nothing like this. I graduate from college in December and I'm pursuing a career in Data Analytics. Your videos are definitely going to help me with my upcoming interviews. God bless you brother! You have helped me to understand SQL on a whole another level!
I have been working two and a half years as a Data Management Officer at icddr,b Bangladesh. Now, I am learning SQL to become a data analyst. I found your video classes very useful, effective and eloquently explained. Thanks techTFQ👌.
This video on the with clause gave me a solution to a query I have been trying to write for a month. I can't thank you enough. Now the query works as it should and is readable! I had tried various unions, exists, partition statements to isolate some data. It did not work, this did! 😀
I have watched many SQL videos and I hardly comment. Thank you and appreciate your videos, I found them very beneficial and what makes real difference in your videos is you explain the rational of why we need to use and its pros and cons. They have unexpectedly answered some of the question that I didn't even exist in my mind. Keep up and great work!
I am obsessed with your channel, I have been working with SQL for so long and I used to run away from window functions but you explain it so nice and clear. I learned by working on it so was never clear on concepts but thank god for your Chanel !
Assalamualaikum taufiq bhai, i have adhd and iv been struggling to learn sql this entire year. I just recently came across your videos and alhamdulillah iv been learning at a rapid pace. I pray that Allah gives you lots of peace and blessings in your life, you have no idea how much you content has helped me Jazakallah
Awesome. I use with all the time. As a data analyst I write for single use code so I often don’t write very efficiently. Using a with clause to get the initial dataset to just look a little bit more like the target dataset and then using it again until I incrementally achieve the target dataset is pretty much my solution. Almost every time.
I was getting hopeless with my sql learning. This channel gave me a new direction. This is the first video that I watched. Now I am going to practice from your sql practice playlist.
Such a wonderful explanation. I was always afraid of using WITH clause but after watching this video, I will definitely try to implement the same wherever it's possible. Thanks a lot brother.
thank you so much man i was just blindly copy pasting the cte queries without understanding how it actually works and I am glad to find this channel, bro explained the concept like we explain everything to 1 year old babies
Thank you for this video, I was using CTE's only for deleting duplicates and I was so confused when I should use these and what they are for. You explained it so well. I'm glad I finally understand CTE.
Another big advantage is when using CTE's for inserting/updating records in multiple rows (One-to-one, or one-to-mamy), you can use the returned id in the other queries. Mutiple modifications in a single query reduce overall latency from a code perspective. Moreover, a CTE is executed as a transaction, so you won't need to start/commit a transaction in your code.
What I mainly like about you is, the project based explanations you provide. Its now making alot of sense to me after watching your videos. Thanks. Please more and more of project based explanation of SQL.
Was always feeling quite hard to get hold of this With clause. Thanks a lot for making it so easy to understand✌️ As usual loved the way you explained the whole thing🙌🏻
Greetings from Vancouver :) Big thank you for this video. As a fintec product manager who uses metabase and its wisiwig (scratch for sql), I was limited severely in what I could do (I know some sql but didn't know this). Your video here finally explains how to create variables, I mean, cte or datasets to work with. I'm binge watching your channel now! Cheers and happy new year to you and yours.
your videos are super. thank you so much. As you're voice is fast, it is bit difficult to make a note of them at the same time. I had to pass the video multiple times and make a note of them, but instead of all that, it would be much better if you could please show a slide in the video itself about the adv/disadv or differences if there are any, so that we can directly make a note of them as per your wordings in the slide. Once again, thank you so much and don't stop making more videos. You're on an online guru for us ...🙏
believe me.. Now i can able to solve complex queries after following techTFQ. i know still longway to go but i have confidence to solve any kind of questions
it was really amazing explanation! since i started fallowing you're videos i can easily say that i have better understnading of how sql works, thank you so much Bro for you're efforts!!!
Everytime I watch your videos I get tempted to subscribe the channel, later making me feel sad that am already subscribed. Is there anyone here who feel crazy like this ?
Thank you very much for the explanation, everything was very clear, I have seen it before, but it was very interesting watching this video and getting a detailed example to get a better understanding.
I use CTE in almost every production query I write. After the first sub query, I always format the alias name with leading comma as , as ( Select …. ) , as ( -- The reason for this is when revisiting the query a year later, I just need to comment out any alias name row and highlight from top to and including the query with the alias commented out, and examine any intermediate results. Excellent technique when a business person wants to see how something is being generated. Use white space above and below these lines. My tip? Format for functionality, use leading commas.
Randomly I was scrolling RUclips ... saw your channel and saw this video it's amazing! the way of explanation is perfect..glad to see your videos Macha Tqu and do more videos on dynamic queries, SP debugging 😊
Thank you very much for this simple and informative course. it helped me solve part of my problem with a database. I am still trying to complete my query of statistics.
Thank you sir, I understand common things, but I need more practice to improve the information that I got from this video, It's complex for me. Very useful video.
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
Please note the COST column used in this table represents the total cost of purchase hence we do not need to multiple quantity with cost to get the total cost. I designed the dataset with an assumption that COST is the total purchase cost.
then it becomes easy...
What if we have to calculate quantity × price first? Does Sum(quantity*price) work?
Yeah your videos are really helping me to understand this course
Hello, please help me reconstruct this using SELECT. The with clause isn’t supported
WITH seq(n) AS
(SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, '20210101', (DATEADD(DAY, -1, DATEADD(YEAR, 30, '20210101'))))),
d(d) AS
(SELECT DATEADD(DAY, n, '20210101') FROM seq)
SELECT d, COUNT (*)
ON a.dogma_registereddate = d OR a.dogma_removeddate IS NULL)
WHERE a.dogma_charitynumber != 'SC000036'
GROUP BY d
ORDER BY d
OPTION (MAXRECURSION 0);
Sir can you please explain why did you give X as alias and also again avg_sales as alias but confused
alot of people just show how to make the query, what I call "watch me type". You actually teach the how and whys. i learned alot with your channel. thanks
You're the best resource I have found! I've taken classes on Udemy and everything but it's nothing like this. I graduate from college in December and I'm pursuing a career in Data Analytics. Your videos are definitely going to help me with my upcoming interviews. God bless you brother! You have helped me to understand SQL on a whole another level!
I am so glad to hear this bro :)
Happy to know my videos have helped you to understand SQL.
Thank you for the amazing feedback :)
That's correct @ItsReflexes, he is awesome 🙂
Thank you once again Akshay 🙏🏼
UPDATE : I signed a job offer with a great compensation range. In addition, I had 3+ companies to choose from. Hard work does pay off!
Congratulations buddy 💐
I am so happy for your success 👌
All the best 👍
Most underrated RUclips channel i have seen, you have rocked all the sql videos man
I have been working two and a half years as a Data Management Officer at icddr,b Bangladesh. Now, I am learning SQL to become a data analyst. I found your video classes very useful, effective and eloquently explained. Thanks techTFQ👌.
One of the best videos on RUclips for SQL Learners.
from zero to super hero with you man, massive thanks to do it easier.
This video on the with clause gave me a solution to a query I have been trying to write for a month. I can't thank you enough. Now the query works as it should and is readable! I had tried various unions, exists, partition statements to isolate some data. It did not work, this did! 😀
I have watched many SQL videos and I hardly comment. Thank you and appreciate your videos, I found them very beneficial and what makes real difference in your videos is you explain the rational of why we need to use and its pros and cons. They have unexpectedly answered some of the question that I didn't even exist in my mind. Keep up and great work!
Thank you so very much for the amazing feedback :)
I am so glad you took the time to comment and so glad you like my contents 🙏
I am obsessed with your channel, I have been working with SQL for so long and I used to run away from window functions but you explain it so nice and clear. I learned by working on it so was never clear on concepts but thank god for your Chanel !
Thank you Kamaljeet 🙏🏼
I am glad to read this comment and happy to know my contents have helped you
i have never seen someone like you to be that perfect in sql ever. hats off taufiq ,god bless you SIR♥
Assalamualaikum taufiq bhai, i have adhd and iv been struggling to learn sql this entire year.
I just recently came across your videos and alhamdulillah iv been learning at a rapid pace.
I pray that Allah gives you lots of peace and blessings in your life, you have no idea how much you content has helped me
Jazakallah
5 minutes in and I already understand more than any other video on this topic. Thanks man
Awesome.. glad this helped
Awesome. I use with all the time. As a data analyst I write for single use code so I often don’t write very efficiently. Using a with clause to get the initial dataset to just look a little bit more like the target dataset and then using it again until I incrementally achieve the target dataset is pretty much my solution. Almost every time.
This is the best video on CTE. Thanks a lot for explaining in a simple way. 🙌
Damn, best and underrated channel I have seen till now. Crisp, to the point and just useful information...
Thank you Sagar once again for all the support and always liking my content 🙏🏼
Am so glad, I am able to add some value through these videos 🙂
You are the best no one explained with clause better than you.
thank you so much :)
I was getting hopeless with my sql learning. This channel gave me a new direction. This is the first video that I watched. Now I am going to practice from your sql practice playlist.
Awesome Sahil 👌
Very happy to read this comment.. I am glad this helped you
Such a wonderful explanation. I was always afraid of using WITH clause but after watching this video, I will definitely try to implement the same wherever it's possible. Thanks a lot brother.
Glad to hear that buddy
you are a gem sir, as soon as I crack my data analyst, I am gonna donate good part of my first month salary to channel, #gratitude
Thank you Saurav :)
Appreciate your generosity. And wishing you all the best
Simple but elegant explanation about WITH clause, thanks for creating these videos and sharing your knowledge .....
Glad you liked it 🙏🏼
Undoubtly your course is the best resource not only in youtube but also whatever available in internet that to be free.
Great Lecture Sir
Best video!!!! 100x better than all univ professors combined! Such a clear cut explanation!
Glad you liked it
thank you so much man i was just blindly copy pasting the cte queries without understanding how it actually works and I am glad to find this channel, bro explained the concept like we explain everything to 1 year old babies
Really useful video. Spared me a lot of web research and let me understand the principle that enabled me to execute at a way more complex query.
Thank you for this video, I was using CTE's only for deleting duplicates and I was so confused when I should use these and what they are for. You explained it so well. I'm glad I finally understand CTE.
glad this helped
Another big advantage is when using CTE's for inserting/updating records in multiple rows (One-to-one, or one-to-mamy), you can use the returned id in the other queries.
Mutiple modifications in a single query reduce overall latency from a code perspective.
Moreover, a CTE is executed as a transaction, so you won't need to start/commit a transaction in your code.
You are the best instructor who explains tough things in a very simple way.
Appreciate your work..!!!
I had to go through this 3x to really understand the with clause concept. Thanks a whole bunch 🎉.
Great Teacher.i never seen a person who explain complex stuff in a easy and simple way.
what i felt about the video ? your a god dude ❤, thats what i felt
THIS IS THE WAY ONE SHOULD TEACH... TOO GOOD
Thank you very much Vikram 🙏🏼
Appreciate your feedback ☺️
By far the best material I ever met. Thank you!
Thank you Coma. glad you liked it :)
What I mainly like about you is, the project based explanations you provide. Its now making alot of sense to me after watching your videos. Thanks. Please more and more of project based explanation of SQL.
Thank you for the feedback 🙏🏼
So glad to read this ☺️
Was always feeling quite hard to get hold of this With clause. Thanks a lot for making it so easy to understand✌️ As usual loved the way you explained the whole thing🙌🏻
Thank you bro ❤️❤️❤️
Greetings from Vancouver :) Big thank you for this video. As a fintec product manager who uses metabase and its wisiwig (scratch for sql), I was limited severely in what I could do (I know some sql but didn't know this). Your video here finally explains how to create variables, I mean, cte or datasets to work with. I'm binge watching your channel now! Cheers and happy new year to you and yours.
Never used it. Fantastic proof of concept. Will be using it as a cursor in future.
Thank you Sudhanshu 🙏🏼
Yes it’s a very very useful concept
Sir plzzz bring more videos like this , your voice has tone that helped me lot . Thank you so much
Thank you Harcharan 🙏🏼
Glad to know my voice is clear enough ☺️☺️
Will make more videos
you explained in very simple way so it is best video so far I ever seen on SQL.
Im grateful to you beyond words could express Mr.Thoufiq. Big Salute to you
GREAT EXPLANATION AND IS BETTER THAN OTHER CHARGEABLE INSTITUTIONS
your videos are super. thank you so much. As you're voice is fast, it is bit difficult to make a note of them at the same time. I had to pass the video multiple times and make a note of them, but instead of all that, it would be much better if you could please show a slide in the video itself about the adv/disadv or differences if there are any, so that we can directly make a note of them as per your wordings in the slide. Once again, thank you so much and don't stop making more videos. You're on an online guru for us ...🙏
Thank you Harish for your kind feedback 🙏🏼
I am glad this helped you and I’ll try to get slower as much as possible
believe me.. Now i can able to solve complex queries after following techTFQ. i know still longway to go but i have confidence to solve any kind of questions
Very happy to know that ☺️
Thank you 🙏🏼
Yea. Best I have watched so far. Thanks for how u convey this information. U crafted!
Best video i have sen for this concept!
Best One to show how really CTEs work and the difference between them and subqueries. Nice Job
thank you
Really Helpful and Very Good to understand SQL especially for Beginners
🎉Perfect demo bro with easiest and clear explanation🎉
All your contents are straight to the point. No nonsense . I checked your blog it was very useful. Thanks keep up the good work
Thank you Manoj 🙏🏼
It’s so nice to see such positive feedback 🙂
So glad you liked the content 🙂
It's great to start the SQL learning career with your video's.
Thank you Prashanth.. glad you think so
Thanks for the generous sharing! Your explanation makes it much easier to grasp than the one i took on Udemy.
Great Work is simplifying complex things simpler. Thank You for doing that.
Best wishes...
Your welcome 🙏🏼
Just like the way you explain the concept so that it is more easier to understand. Thanks again for your video. I have learnt a lot.
YOU ARE THE BEST TUTOR.
Simple and clear explanation for usage of CTE. Thanks
Glad you liked it
What an explanation 🔥 This is the most simplest explanation I have ever seen in my career. Thank You so much ❤️
Please make a video on table indexes!
Thank you Akash 🙏🏼
Glad you liked it ..
Will consider a video on index in the near future
@@techTFQ yes please...
Bro You're an Amazing Teacher. Nobody ever could make learning SQL so simple!.
Thank You!
CLEAR AND SIMPLE. LOVELY EXPLANATION
Your SQL YT videos has helped me a lot, thank you
With query has been clearly explained, thanks for this video
Glad you liked it
Thank you so much for providing such a fabulous content.. I am totally obsessed with your channel.
Thank you Surbhi 🙏🏼
I am glad you liked it
it was really amazing explanation! since i started fallowing you're videos i can easily say that i have better understnading of how sql works, thank you so much Bro for you're efforts!!!
Glad to hear that Anand ☺️
Explain everything so clearly with all of the explanation that helped a lot to understand the concepts Better. Thanks a lot for such valuable videos.👍
Your welcome Asma
Glad this helped
thx for the videos, they really help to understand how sql querries could be written!
Thank you 🙏🏼
Glad this helped
Learning from you each day, my friend. Great content, not only in this video but other SQL videos as well. Keep up the good work!
What an amazing explaination..it really helps me to understand SQL well ☺Thank you so much sir🌺🌸
thanx techTFQ for your simplicity.
Thank you bro 🙏🏼
Glad you liked it 🙂
Everytime I watch your videos I get tempted to subscribe the channel, later making me feel sad that am already subscribed. Is there anyone here who feel crazy like this ?
haha awesome Nibin :D
Thanks a lot
Thank you very much for the explanation, everything was very clear, I have seen it before, but it was very interesting watching this video and getting a detailed example to get a better understanding.
Glad it was helpful!
Thank you so much.
God is so good as you are a blessing with your lessons.
You have made it so easy to understand the with clause. Thank You!
Your welcome 🙏🏼
Glad this helped
I use CTE in almost every production query I write. After the first sub query, I always format the alias name with leading comma as
, as (
Select ….
)
, as (
--
The reason for this is when revisiting the query a year later, I just need to comment out any alias name row and highlight from top to and including the query with the alias commented out, and examine any intermediate results. Excellent technique when a business person wants to see how something is being generated. Use white space above and below these lines. My tip? Format for functionality, use leading commas.
Your videos are next level. I couldn't find this quality even in paid courses on udemy
Thank you very much Anas 🙏🏼
Please continue doing this ! Great tutorial Thank you so much !!
Your welcome Ashish
Glad this helped
Randomly I was scrolling RUclips ... saw your channel and saw this video it's amazing! the way of explanation is perfect..glad to see your videos Macha Tqu and do more videos on dynamic queries, SP debugging 😊
super bro, got clear idea on CTE and its applications, Thanks a ton !!
sir u are great,ur learning procedure awasome
Thank you for your brilliant examples and explanations, you are doing an excellent job.
Thank you so much 🙏🏼
Happy to know this helped you
Bro your videos are very helpful.
Keep sharing knowledge..
Thanks
Thank you Shafique 🙏🏼
Will do my best ..
its very useful sir, explanation are very clear and understanging easily
Thank you very much for this simple and informative course. it helped me solve part of my problem with a database. I am still trying to complete my query of statistics.
I am glad to hear that bro
Tq, I learnt something different new thing from this video. Very helpful👌
My pleasure 😊
Thanks for the video, needed to understand this for my job. Very well explained and the way you give the lecture is excellent!
Thank you so much 🙏🏼
Glad you liked it..
This is soooo helpful!! Thank you Thoufiq, you are a God sent :D
you are the GOAT
Great Job Bro... Heart fully Appreciating your Knowledge...
Thank you 🙏🏼
Really thanks bro, your explanation is awesome, It is like a spoon feeding to a born baby.
thanks toufiq..you explain it in detail...
Thank you bro
Thank you sir, I understand common things, but I need more practice to improve the information that I got from this video, It's complex for me. Very useful video.
Thank you. You explained very clearly and also in detail at the same time. Glad I found this video.
Brother your explanation is very clear and understandable. 👍👍👍
Thank you bro 🙏🏼
Your videos are best source to learn SQL. Thank you to share these videos with us.
You're a saviour ❤🙂
Please make more SQL videos on how to solve and write complex queries
Thank you Ramya!
Will do and glad this helped..
Your way of teaching and explaining is too good and helpful. keep it up
You're the best SQL instructor in RUclips. Hoping for the best and keep up this mighty work.
your tutorial helped me a lot thank you so much
Your welcome buddy
GRATITUDE for all the videos in sql complex queries.
Really an excellent way to explain with clause. Thank you for this!
thank you soo much for this video , really helpful for me😃
You're so welcome Sneha!
Thank You for this video...very well explained
Glad you liked it