Learn SQL, How to use window functions using over and Partition By. Find the Percent of Total and Sub Total. #sql, #rdbms, #table, #amitchandakda #interviewquestions #interview #index
SQL Tutorial 14: Window Functions Part 1- Sum Over, Partition By
Definition Source: Google Search, Wikipedia, IBM, W3School
00:00 Introduction
00:30 Window Functions
03:30 Percent of Total
14:00 Percent of Sub Total
Last Video
SQL Tutorial 13: TOPN, Percent of SubTotal/Total Avg of Sum, and filter aggregated data: [ Ссылка ]
select *
from sales
select * , sum(qty) over()
from sales
select sum(qty)
from sales
select City_Id , sum(qty) sqty
from sales
group by City_Id
select * , sum(sqty) over()
from (
select City_Id , sum(qty) sqty
from sales
group by City_Id
) A1
select City,sqty , sum(sqty) over() GT,(sqty*1.0/(sum(sqty) over() ))*100 P_GT
from (
select g.City , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.City
) A1
order by sqty desc;
select State,sqty , sum(sqty) over() GT,(sqty*1.0/(sum(sqty) over() ))*100 P_GT
from (
select g.State , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State
) A1
order by sqty desc
select State,sqty , sum(sqty) over() GT,(sqty*1.0/(MaX(sqty) over() ))*100 P_GT
from (
select g.State , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State
) A1
order by sqty desc
select State,sqty , sum(sqty) over() GT,(sqty*1.0/(Min(sqty) over() ))*100 P_GT
from (
select g.State , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State
) A1
order by sqty desc
select State,sqty , sum(sqty) over() GT,(sqty*1.0/(Avg(sqty) over() ))*100 P_GT
from (
select g.State , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State
) A1
order by sqty desc
select State,sqty , sum(sqty) over() GT,((Sum(sqty) Over()*1.0)/(Count(sqty) over() ))*100 P_GT
from (
select g.State , sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State
) A1
order by sqty desc
select g.State , g.City, sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State, g.City
order by 1
select *, sqty/((sum(Sqty) Over(Partition by State) ))*100 P_ST ,
sum(Sqty) Over(Partition by State) ST
from
(select g.State , g.City, sum(qty*price) sqty
from sales s
inner join geography g on s.City_Id = g.City_Id
group by g.State, g.City) A
-~-~~-~~~-~~-~-
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
[ Ссылка ]
-~-~~-~~~-~~-~-
SQL Tutorial 14: Window Functions Part 1- Sum Over, Partition By
Теги
sqlsql tutorialsql for beginnerssql tutorial for beginnerslearn sql for beginnerssql basics for beginnerssql beginnerssql serversql complete tutorialsql course for beginnersintroduction to sqlsql beginnerlearn sql queriesazure sql databasessql tutorialsbasics of sqllearn sqlsql percent of totalsql window functionssql sum oversql partition bywindow function in sqlwindow functionswindow functions sqlsql analytic function