SQL WITH Clause | How to write SQL Queries using WITH Clause | SQL CTE (Common Table Expression)

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.

How can I get the data set used in this video as in your blogs it is not available?

21/10/23

-- 1
select * from(select id,store_name,sum(cost) as sales from apple
group by id,store_name)t
where t.sales >= (select avg(a.c_sum)from(select id,sum(cost) as c_sum from apple
group by id) a);
-- 2
with s as (select id,store_name,sum(cost) as sales from apple
group by id,store_name)
select * from s
where sales >= (select avg(sales) from s);

----- Simplified Query -------- without so many confusing alias -_-
Question :: find stores whose sales are better than the average sales across all the stores
-- total sales per store
-- avg sales across all the stores
-- comparison

Answer::

with total_sales (store_name, total_sales_per_store) as
(select store_name, sum(price) from sales group by store_name),

avg_sales (avg_sales_across_all_stores) as
(select cast(avg(total_sales_per_store) as int) from total_sales)

select * from total_sales ts
Join avg_sales av
on ts.total_sales_per_store > av.avg_sales_across_all_stores

way too fast... my brain can't process these many information in 2-3 sec

Great explanation sir, can you please make 1 video on group concate in deep 😊

oh how rare seeing a programmer with ok english accent

i have never seen someone like you to be that perfect in sql ever. hats off taufiq ,god bless you SIR♥

Your Explanations were concise and on point. very clear explanations. youre a perfect guide for SQL @techTFQ Thanks!!

Your Link is not working to Download all the scripts

So when to use with Clause and When to use Sub Query?

GRATITUDE for all the videos in sql complex queries.

Hi Taufiq,
can you please tell me why my cast function is giving me error, when I am trying to cast it to Integer?

select cast(avg(salary)as int) as abc from employees;

thanks sir. May u live long.

the script given in description cannot be found

sir u are great,ur learning procedure awasome

Query 2 code : create table sales(
store int,
store_name varchar(256),
product varchar(256),
quantity int,
cost int
);
insert into sales(store,store_name,product,quantity,cost) values
(1, 'apple originals 1', 'iPhone 12 Pro', 1, 1000),
(1, 'apple originals 1', 'Macbook Pro 13', 3, 2000),
(1, 'apple originals 1', 'Airpods Pro', 2, 280),
(2, 'apple originals 2', 'iPhone 12 Pro', 2, 1000),
(3, 'apple originals 3', 'iPhone 12 Pro', 1, 1000),
(3, 'apple originals 3', 'Macbook Pro 13', 1, 2000),
(3, 'apple originals 3', 'Macbook Air', 4, 1100),
(3, 'apple originals 3', 'iPhone 12', 2, 1000),
(3, 'apple originals 3', 'Airpods Pro', 2, 280),
(4, 'apple originals 4', 'iPhone 12 Pro', 2, 1000),
(4, 'apple originals 4', 'Macbook Pro 13', 1, 2500);

You should give the tables link in the description box .It will be better

No need to use join instead use where condition and compare one subquery with other

Need table details

if u can post some videos on query optimization
that will be great

Good day! Where can I get the data? The URL has an error when I opened it.

Excellent Tutorial

This is soooo helpful!! Thank you Thoufiq, you are a God sent :D

Incredible teaching!!

Hi Thoufiq
Thank you so much for great and very clear explanation on SQL and career for DATA ANALYST. I learnt SQL concepts from your channel, i can easily understand concepts but when i practice in leetcode i am unable to do what function should be used and how to follow the steps to find the solution. Can you please make a video which describes how see the problem and do it in step by step order?

😊😊thank you so much

CLEAR AND SIMPLE. LOVELY EXPLANATION

Hi Tawfeeq @techTFQ really grateful to have found your videos' on SQL, no one could have explained better and so much in depth. Do you also conduct online bootcamps on SQL / Python? Would like to learn from you.

very confusing first five minutes into the video. Thanks though.

this is interesting topic , i enjoyed the video while practicing it on my own as well.
(and yours ears are like PK😅😁)

cant we alias total sales in without with clause query @techtfq

Can someone write a sql query of how many time 'basically' is used in the video?

Nice explanation

Bhai thorasa slow Bolna.

I have a doubt, In line no. 15, why we are giving alias X, I tried without alias, it was throwing an error like "every derived table must have its own alias", what would be the reason behind it?

Please help me to get a clear picture

Everything is good but only problem Sir you're speaks very fast please teach in slow mode😝

Excellent video, I am learning lot of things from you. If you can also provide some practice questions as well like one you solve in your videos. Thank you so much for such very well explained conceptual SQL videos.

Best video i have sen for this concept!

May Allah Bless You Sir ❤

I am trying to run this below query --
with empt as (Select First_name from employees)
but showing the below error
(ERROR: syntax error at end of input
LINE 1: with empt as (Select First_name from employees)
^
SQL state: 42601
Character: 49)
----


Can you please let me know what am I doing wromng?

Nice explanation of CTEs!

awesome😮

Really Helpful and Very Good to understand SQL especially for Beginners

Really awesome videos man thanks for this session.

Hello sir, thanks for your help to make us understand the CTE in most easy and best way.

I would like to know if is there any sql server complete course you are providing on udemy or somewhere?

Nice

Very good video