Home
Search results “Oracle sum over date range”
Part 5   SQL query to find employees hired in last n months
 
04:53
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-5-sql-query-to-find-employees.html This question is asked is many sql server interviews. If you have used DATEDIFF() sql server function then you already know the answer. -- Replace N with number of months Select * FROM Employees Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N
Views: 190539 kudvenkat
Difference between rows and range
 
05:52
range vs rows in sql server difference between rows clause and range clause in sql server range clause vs rows clause in sql server sql server running total query running total example in sql server In this video we will discuss the difference between rows and range in SQL Server. This is continuation to Part 116. Please watch Part 116 from SQL Server tutorial before proceeding. Let us understand the difference with an example. We will use the following Employees table in this demo. SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Salary int ) Go Insert Into Employees Values (1, 'Mark', 1000) Insert Into Employees Values (2, 'John', 2000) Insert Into Employees Values (3, 'Pam', 3000) Insert Into Employees Values (4, 'Sara', 4000) Insert Into Employees Values (5, 'Todd', 5000) Go Calculate the running total of Salary and display it against every employee row The following query calculates the running total. We have not specified an explicit value for ROWS or RANGE clause. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS RunningTotal FROM Employees So the above query is using the default value which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW This means the above query can be re-written using an explicit value for ROWS or RANGE clause as shown below. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees We can also achieve the same result, by replacing RANGE with ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees What is the difference between ROWS and RANGE To understand the difference we need some duplicate values for the Salary column in the Employees table. Execute the following UPDATE script to introduce duplicate values in the Salary column Update Employees set Salary = 1000 where Id = 2 Update Employees set Salary = 3000 where Id = 4 Go Now execute the following query. Notice that we get the running total as expected. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees The following query uses RANGE instead of ROWS SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Employees Notice we don't get the running total as expected. So, the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity. All together side by side. The following query shows how running total changes 1. When no value is specified for ROWS or RANGE clause 2. When RANGE clause is used explicitly with it's default value 3. When ROWS clause is used instead of RANGE clause SELECT Name, Salary, SUM(Salary) OVER(ORDER BY Salary) AS [Default], SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range], SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows] FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range_8.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 37509 kudvenkat
SQL Tutorial - Window Functions - Calculate Running Totals, Averages
 
13:06
For the best book on T-SQL click here: https://www.amazon.co.uk/gp/product/0735685045/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=0735685045&linkCode=as2&tag=beardeddev-21&linkId=2e450454659d4279f54dd188d24bc011 Another fantastic SQL Tutorial brought to you by BeardedDev. If you are new to working with Window Functions check out this video: https://youtu.be/H6OTMoXjNiM In this video we learn how to use Window Functions to calculate running totals and running averages. This video teaches about Window Frames: Rows Range Preceding Current Row Following Window Frames are a filtered portion of a partition. Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012. Window Functions can only be included within SELECT or ORDER BY clauses. Functions Available: Aggregate - COUNT, SUM, MIN, MAX, AVG Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST Windows Functions also have FRAMES ROWS RANGE Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future. SQL: SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date SELECT Sales_Id , Sales_Date , Sales_Total , SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total] , CAST(AVG(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS DECIMAL(8, 2)) AS [Running Average] FROM dbo.Sales_2 WHERE Sales_Cust_Id = 3 ORDER BY Sales_Date
Views: 4906 BeardedDev
Calculate running total in SQL Server 2012
 
06:23
running total sql server cumulative total in ms sql server running total column In this video we will discuss how to calculate running total in SQL Server 2012 and later versions. We will use the following Employees table for the examples in this video. SQL Script to create Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 5000) Insert Into Employees Values (2, 'John', 'Male', 4500) Insert Into Employees Values (3, 'Pam', 'Female', 5500) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 3500) Insert Into Employees Values (6, 'Mary', 'Female', 5000) Insert Into Employees Values (7, 'Ben', 'Male', 6500) Insert Into Employees Values (8, 'Jodi', 'Female', 7000) Insert Into Employees Values (9, 'Tom', 'Male', 5500) Insert Into Employees Values (10, 'Ron', 'Male', 5000) Go SQL Query to compute running total without partitions SELECT Name, Gender, Salary, SUM(Salary) OVER (ORDER BY ID) AS RunningTotal FROM Employees SQL Query to compute running total with partitions SELECT Name, Gender, Salary, SUM(Salary) OVER (PARTITION BY Gender ORDER BY ID) AS RunningTotal FROM Employees What happens if I use order by on Salary column If you have duplicate values in the Salary column, all the duplicate values will be added to the running total at once. In the example below notice that we have 5000 repeated 3 times. So 15000 (i.e 5000 + 5000 + 5000) is added to the running total at once. SELECT Name, Gender, Salary, SUM(Salary) OVER (ORDER BY Salary) AS RunningTotal FROM Employees So when computing running total, it is better to use a column that has unique data in the ORDER BY clause. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/calculate-running-total-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/calculate-running-total-in-sql-server_3.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 61677 kudvenkat
TSQL: Get Previous Day's/Week's Value
 
14:02
How to get the previous day's/week's/month's/year's values. Note that in SQL Server 2012, we have the LAG and LEAD functions and use those for previous values.
Views: 2059 SQLInSix Minutes
Simplifying Effective Date Range Queries with Temporal Validity with Chris Saxton and Stew Ashton
 
01:00:22
Storing changes to data over time is a common requirement. For example, many businesses like to keep a historic record of customer address changes or product price updates. Data warehouse developers frequently need to store history in slowly changing dimensions. Typically these requirements are implemented by adding effective "from" and "to" date columns to tables. This requires developers to write convoluted select statements to find the rows active at a given point in time. This is error-prone and open to interpretation by developers, making applications harder to maintain. Introduced in 12c, temporal validity provides a standard way to write queries using effective date ranges. Implementing this helps improve developer productivity by simplifying SQL and providing a consistent method for managing time series data. Watch this session to see how you can easily adopt this on your 12c databases.
Views: 210 ODTUG
SQL Aggregation queries using Group By, Sum, Count and Having
 
10:01
From SQL Queries Joes 2 Pros (Vol2) ch4.1. Learn up to write aggregated queries.
Views: 189800 Joes2Pros SQL Trainings
How to Fetch Latest record/row from the Table in Oracle Database
 
03:32
You will learn how to How to Fetch Latest record/row from the Table in Oracle Database. Realtime Scenario: This logic can be implemented to find whether the latest record should be inserted or updated to datawarehouse
Calculating A Running Total With SQL
 
10:10
SQL is the language of data. It can be used to answer countless questions in any company (unless that company still runs 100% off mainframes). The ubiquity of data makes SQL one of the most utilized technical skills whether you are a data engineer, analyst, or data scientist. One of the problems you may have to solve is calculating a rolling sum. There are many ways to approach this problem. You can use an analytic function, a self join, a subquery and even simply design. If you have any questions or comments on SQL please feel free to send them! Also, if you have any problems you want solved with SQL please let us know. Problem Solving With SQL http://www.acheronanalytics.com/acheron-blog/january-10th-2019
Views: 771 Ben R
SQL– Calculating Running Total Cumulative Total
 
02:57
In this short video we learn on how to compute a Running Total field in SQL. -- Step 1: Create the Temp Dataset if object_id ('tempdb..#RunningTotal') is not null drop table #RunningTotal Create table #RunningTotal (id int,[Value] int) go insert into #RunningTotal (id,[Value]) values (0,1) insert into #RunningTotal (id,[Value]) values (1,1) insert into #RunningTotal (id,[Value]) values (1,1) insert into #RunningTotal (id,[Value]) values (1,1) insert into #RunningTotal (id,[Value]) values (1,1) insert into #RunningTotal (id,[Value]) values (2,1) insert into #RunningTotal (id,[Value]) values (2,1) insert into #RunningTotal (id,[Value]) values (2,1) insert into #RunningTotal (id,[Value]) values (3,1) insert into #RunningTotal (id,[Value]) values (3,1) -- Step 2: Verify the test data select * from #RunningTotal -- Step 3: Running Total Computation select id, [value], sum([value]) over (order by id rows unbounded preceding) as RunningTotal ,sum([value]) over (partition by id order by id rows unbounded preceding) as RunningTotal_PerId --,sum([value]) over (partition by id order by id range unbounded preceding) as RangeOver_RunningTotal_PerId --,sum([value]) over (order by id range unbounded preceding) as RangeOver_RunningTotal_PerId from #RunningTotal
Views: 101 Siddharth Mishra
Year-to-Date using Partition By with Windowing - SQL Training Online
 
05:43
You can use the Partition By Function in SQL Server to get a Year-to-Date and Month-to_Date calculation. In this video I show you how to create a Year-to-Date value using the Windowing Partition By Function in TSQL. READ THE ORIGINAL ARTICLE WITH SQL SCRIPTS HERE http://www.sqltrainingonline.com/year-to-date-using-the-partition-by-function-with-windowing-in-sql-server/ YOUTUBE NEWS UPDATES http://www.youtube.com/user/sqltrainingonline VISIT SQLTRAININGONLINE.COM FOR TONS MORE VIDEO NEWS & TIPS http://www.sqltrainingonline.com SUBSCRIBE FOR OTHER SQL TIPS AND NEWS! http://www.youtube.com/subscription_center?add_user=sqltrainingonline SUBSCRIBE TO OUR EMAIL LIST! http://www.sqltrainingonline.com LET'S CONNECT! Google+: http://gplus.to/SQLTrainingOnline Facebook: http://facebook.com/SQLTrainingOnline Twitter: http://twitter.com/sql_by_joey Linkedin: http://linkedin.com/in/joeyblue SQLTrainingOnline: http://www.sqltrainingonline.com
Views: 3746 Joey Blue
SQL: Group By,  Having, Min, Max Sum
 
14:43
Learn to summarize yoru data using the SQL Aggregates such as count and sum and the GROUP BY and HAVING clauses of the SELECT statement
Views: 160890 Michael Fudge
Oracle COUNT Function
 
04:43
https://www.databasestar.com/oracle-count/ The Oracle COUNT function is used to count the number of rows from the column, or number of values, provided to it. It’s commonly used to find the number of rows returned by a SELECT query. The syntax of the COUNT function is: COUNT ( [ * | [ DISTINCT | ALL ] expression) [ over (analytic_clause) ] There’s a lot of square brackets here, indicating optional parameters and choices. This just means you can use the COUNT function in a few different ways: COUNT(*) COUNT(DISTINCT expression) COUNT(ALL expression) COUNT(*) OVER (analytic_clause) COUNT(DISTINCT expression) OVER (analytic_clause) COUNT(ALL expression) OVER (analytic_clause) It can be used as an aggregate or analytic function. The parameters of the COUNT function are: - expression: The expression to count the values of. This can be a column or a set of values, for example. - analytic_clause: This is used to specify the criteria for an analytic query. COUNT(*) is the most common way to use this function, I’ve found. As COUNT(*) is an aggregate function, like all aggregate functions the columns mentioned in the SELECT clause need to exist in the GROUP BY clause. NULL values are ignored by COUNT(expression), but they are counted when using COUNT(*). COUNT(*) has the exact same calculation and performance as COUNT(1). For more information about the Oracle COUNT function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-count/
Views: 607 Database Star
Run SQL Query with Date Range filter in Excel
 
05:38
Run SQL Query with Date Range filter in Excel My SQL Blog: https://sqlwithmanoj.com/ Check my SQL FB Page at: https://www.facebook.com/sqlwithmanoj
Views: 17502 SQL with Manoj
Excel Magic Trick 870: VLOOKUP A Date and Pattern To Simplify Formula
 
02:31
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn how to: 1. : VLOOKUP A Date and Pattern To Simplify Formula
Views: 40821 ExcelIsFun
T-SQL: Over() and Partition By
 
04:17
Show how to use OVER and PARTITION BY to get groups of data with aggregation.
Views: 10805 MidnightDBA
SQL Tutorial - Window Functions - Lag and Lead
 
08:10
Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis. In this video I talk about using Lag and Lead in Windows Functions. This video covers the requirements to pass the exam: If you would like to follow along with the tutorial please run the SQL below: IF OBJECT_ID(N'dbo.Sales', N'U') IS NOT NULL DROP TABLE dbo.Sales; GO CREATE TABLE dbo.Sales ( Sales_Id INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Sales_Sales_Id PRIMARY KEY , Sales_Customer_Id INT NOT NULL , Sales_Date DATETIME2 NOT NULL , Sales_Amount DECIMAL (16, 2) NOT NULL ) INSERT INTO dbo.Sales (Sales_Customer_Id, Sales_Date, Sales_Amount) VALUES (1, '20180102', 54.99) , (1, '20180103', 72.99) , (1, '20180104', 34.99) , (1, '20180115', 29.99) , (1, '20180121', 67.00) Lag and Lead are useful for performing trend analysis, in the example I show how we can display a customer spending trend. Lag will show the previous value. Lead will show the next value. Lag and Lead accept multiple parameters as demonstrated in the video: LAG([Column], [Offset], [Value if NULL]) The example of LAG and LEAD in the video can be shown by executing the below SQL query: SELECT Sales_Customer_Id , Sales_Date , LAG(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS PrevValue , Sales_Amount , LEAD(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS NextValue FROM dbo.Sales Please feel free to post comments.
Views: 3134 BeardedDev
OVER clause in Oracle Database
 
08:00
OVER clause in Oracle
Views: 71 Adam Tech
Cumulative Sum Calculation in Oracle Database
 
09:24
cumulative sum in oracle find cumulative salary of an employee cumulative salary meaning
Views: 362 Adam Tech
Creating a Report Showing Data Between Dates Ranges on Microsoft Access
 
08:34
In my previous video on Microsoft Access I shown you how to create a simple input form, In this video I showing you how to manipulate that information and show you all the data between a range of dates. The scenario in this video is showing you all the deliveries made between 2 delivery Dates. For example; What deliveries did I make between 05/05/2014 and 30/05/2014. If this helped you don't forget to like the video and subscribe to the channel, if there is anything I can help you with please leave a comment at the bottom or send me a message. If you have any other questions based on Microsoft Access don't hesitate to ask me! I'm always looking for new ideas to help you all out!
SQL Server Calculating Cumulative Sums
 
04:47
7. Calculating Cumulative Sums
Views: 2743 Eagle
Analytical Functions in oracle explained with real examples
 
25:24
This video explains analytical functions and how they are implemented in real projects. Analytical functions are somewhat similar to aggregate functions,but they offer much more. Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function. I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions. You can find the sample problems along with dataset in the below link. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Views: 28967 Tech Coach
Oracle DENSE_RANK Function
 
04:09
https://www.databasestar.com/oracle-dense_rank/ The Oracle DENSE_RANK function allows you to calculate a rank of a row in a group of rows. It returns this rank as a NUMBER value. This function can be used as either an analytic or an aggregate function, and the syntax depends on how you use it. As an aggregate function it looks like this: DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] ) These parameters are: - expr: this is the value to search for in a particular table. You can specify multiple search values, and just separate them by a comma. - order_expr: this is used to order the expr values by, because you get a different results depending on what order you rank the values in. - NULLS FIRST or LAST: this is where you can specify if you want to have NULL values appear at the top of your ranking order or at the bottom. As an analytic function, it looks like this: DENSE_RANK() OVER ( [query_partition_clause] order_by_clause) The parameters are: - query_partition_clause: this is the expression that the ranking is “grouped” by, as analytic functions allow you to group data within rows. - order_by_clause: this is the expression to order your results by to determine a rank. For this function, if two records have the same sort or rank position, they will have the same RANK value. It won’t cause a gap in the rankings, unlike the RANK function which does cause a gap. For more information about the Oracle DENSE_RANK function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-dense_rank/
Views: 553 Database Star
Create Queries with Calculated Fields and Aggregate Functions
 
08:56
Create a new field in an Access database query that is a formula/calculation based off of an existing field. Use aggregate functions like average and sum to display summary data about query results.
Views: 469372 Ralph Phillips
PIVOT Tutorial - generating dynamic column with PIVOT Interview Question SQL
 
14:14
In this lecture you will learn about PIVOT operator and using it to generate dynamic columns.PIVOT is very use full operator to convert values into columns. PIVOT: •Convert your output from (values)vertical order to horizontal(making columns) one Topic to cover: •PIVOT example •PIVOT syntax •Interview Question •PIVOT on northwind •Dynamic PIVOT query http://www.techsapphire.in/index/pivot_sql_lesson_with_generating_dynamic_columns_with_pivot/0-158
Views: 32833 techsapphire
How to Code Oracle Analytic Functions
 
52:40
Many more free Oracle Database and SQL tutorials at http://skillbuilders.com/free-oracle-tutorials. SQL is a language that allows you to perform queries to answer a wide range of questions about your data. While standard SQL is quite powerful, there are many questions that it cannot answer at all or that it can only answer with complex and inefficient SQL workarounds. Questions about running totals, percent of total, and ranking within a group fall into this area. Also, more complex issues such as Top-N within a group and aggregates over a range also fall in this arena. These questions can be easily answered via a new class of SQL statements called Analytic Functions. This presentation will show you how to use Oracle's Analytic Function extensions to SQL to answer these questions simply and efficiently.
Views: 26606 SkillBuilders
What the PARTITION BY clause does in Analytic Functions Oracle SQL
 
09:14
In this tutorial video we will learn how to use PARTITION BY clause in Analytic Functions Oracle SQL
Views: 2172 Spod ssppoodd
How to Code Oracle FIRST LAST Aggregate Functions
 
06:35
Learn Advanced Oracle SQL Aggregate functions FIRST and LAST. View all lessons in this tutorial at http://www.skillbuilders.com/advanced-aggregations-with-oracle-sql
Views: 881 SkillBuilders
Rows Unbounded Preceding, Following in SQL Server
 
11:35
Click here to Subscribe to IT PORT Channel : https://www.youtube.com/channel/UCMjmoppveJ3mwspLKXYbVlg From SQL Server 2012, This feature enabled to OVER BY Windowing Functions The ROWS limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause ROWS BETWEEN 1 PRECEDING AND CURRENT ROW – Aggregates 2 Between Last Row and Current Row ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING – Aggregates 2 Current Row and Next Row ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW – Aggregates all Rows before Current Row with Current Row ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING – Aggregates all the Rows After Current Row With Current Row ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING – Aggregates all Rows
Views: 1724 IT Port
Select Rows With Max Value By Group SQL Interview Question
 
10:06
How to select the max value from each set using group by in the free Adventure Works database. This example is done using sql server using SQL Server Management Studio. This is a common interview question. How to install SQL Server Management Studio and set up Adventure Works: https://www.youtube.com/watch?v=b6yNwGC7CY4&t=
Views: 1621 Blake B
SQL in Oracle 11g Tutorial #11: Aggregate Function(MAX,MIN,SUM,AVG,STDDEV,VARIANCE) in SQL
 
03:31
SQL in Oracle 11g Tutorial #11: Aggregate Function(MAX,MIN,SUM,AVG,STDDEV,VARIANCE) in SQL. Thanks for Watching my Channel “Learn TechToTech”. Please subscribe my channel for getting first updates after uploading video.Social Media pages of Channel are: 1. My Website : www.learnfromrakesh.com 2. My Twitter: https://twitter.com/LearnTechtotech 3. Facebook Page: https://www.facebook.com/LearnTechToTech 4. Blogger : https://learntechtotech.blogspot.in/ 5. Google+ : https://learntechtotech.blogspot.in/ 6. Pinterest : https://in.pinterest.com/LearnTechToTech/ 7. LinkedIn: https://www.linkedin.com/in/Learntechtotech/ Playlist of Different Technology: 1. Operating System : https://goo.gl/q6SfrW 2. Python Programming Language : https://goo.gl/L8b5dc 3. C Language : https://goo.gl/SwvDu9 4. C language for Placement: https://goo.gl/AaQBa4 5. Java: https://goo.gl/M8F2uy 6. MySql : https://goo.gl/vdJbHQ 7. Android Mobile Application Development: https://goo.gl/M6woaT 8. Kotlin Programming Language : https://goo.gl/GXE5cd 9. Go Programming: https://goo.gl/Ua3xYW 10. Internet of Things(IoT): https://goo.gl/f2afkY 11. Oracle 11g : https://goo.gl/zds8r2 12. C++ : https://goo.gl/C1psMT
Views: 104 Learn TechToTech
Part 12   Can we join two tables without primary foreign key relation
 
10:38
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/09/part-12-can-we-join-two-tables-without.html Can we join two tables without primary foreign key relation Yes, we can join two tables without primary foreign key relation as long as the column values involved in the join can be converted to one type. ID column in Departments table is not the primary Key and DepartmentId column in Employees table is not the foreign key. But we can still join these tables using ID column from Departments table and DepartmentId column from Employees table, as both the columns involved in the join have same data type i.e int. Select Employees.Name as EmployeeName, Departments.Name as DepartmentName from Employees join Departments on Departments.ID = Employees.DepartmentId The obious next question is, if primary foreign key relation is not mandatory for 2 tables to be joined then what is the use of these keys? Primary key enforces uniqueness of values over one or more columns. Since ID is not a primary key in Departments table, 2 or more departments may end up having same ID value, which makes it impossible to distinguish between them based on the ID column value. Foreign key enforces referential integrity. Without foreign key constraint on DepartmentId column in Employees table, it is possible to insert a row into Employees table with a value for DepartmentId column that does not exist in Departments table. The following insert statement, successfully inserts a new Employee into Employees table whose DepartmentId is 100. But we don't have a department with ID = 100 in Departments table. This means this employee row is an orphan row, and the referential integrity is lost as result Insert into Employees values (8, 'Mary', 'Female', 80000, 100) If we have had a foreign key constraint on DepartmentId column in Employees table, the following insert statement would have failed with the following error. Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "Sample", table "dbo.Departments", column 'ID'.
Views: 128196 kudvenkat
Excel: How to Best Import External Data into Excel & Import data from the Web to Excel
 
11:16
Get the full course: https://courses.xelplus.com Excel has various tools that can help you easily clean up your raw data for further analysis. Apart from using formulas and formatting, there are two useful Excel features that let you easily clean up. One method is text to columns feature and the other to import external data. The Get External Data tool can be used to import and analyse data from other systems. This tool is also useful for importing data to run your dashboards or do further analysis using pivot tables, etc. Usually you can get data extracts from any system (e.g. “.dat” or “.csv” from Oracle, “.xml” files from SAP, etc.). These can be imported into Excel. Importing External Data also gives you control over the decimals and thousand separator, especially if you’re dealing with international companies. In the US, the decimal is dot and the comma is a thousand separator and in most European countries it’s the other way round, where the comma is a decimal and the dot a thousand separator. It’s always safest to Import it properly with the Excel Data Import wizard! After importing press Ctrl + T to create a table out of it. You can create a pivot table off this one, and when you do, even if you add more data to it, the pivot reflects the new data. Importing Data from the Web / Internet into Excel – creating web queries that refresh automatically: In Excel you can also import data directly from the web. You basically create a web query which you set to refresh automatically based on your preference. ★ My Online Excel Courses ► https://courses.xelplus.com/ ✉ Subscribe & get my TOP 10 Excel formulas e-book for free https://www.xelplus.com/free-ebook/ EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Views: 150501 Leila Gharani
Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records
 
18:02
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import and transform Data Set, including removing Columns, Filter For Records and adding an extra Column to calculate Net Revenue using the Number.Round Power Query Function. 3. (06:38) Load Report to Sheet 4. (07:09) Import each Parameter/Criteria Table. 5. (07:36) Convert each Criteria Table to a single Text Item using the Record.Field Power Query Function. 6. (11:38) Declare and Insert Variables into M Code for Report 7. (14:10) Test Reporting System 8. (14:30) Create Recorded Macro To Refresh All and assign it to a Form button 9. (16:40) Test Macro and Reporting System 10. (17:25) Summary
Views: 90867 ExcelIsFun
Oracle LISTAGG Function
 
04:57
https://www.databasestar.com/oracle-listagg/ The Oracle LISTAGG function allows you to aggregate or combine string values from multiple rows into a single row. For example, it turns this data: FIRST_NAME Adam Brad Carrie Into this data: FIRST_NAME Adam, Brad, Carrie The same data is shown, but it’s in one row and not multiple. It’s a valuable string manipulation function, and once you know what it is and how it works, you’ll be able to use it in your queries as needed. So what does it look like? The function is: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] These parameters are: measure_expr: This is a column or expression that you want to concatenate the values of. In the example above, it was the first_name column. Mandatory. delimiter: This is the character between each of the measure_expr values. Optional, the default is a comma. order_by_clause: This is the order that the values from the measure_expr are listed. Mandatory. query_partition_clause: This allows you to use LISTAGG as an analytic function, allowing you to show LISTAGG in groups for different rows. One thing to be aware of is that the output of the Oracle LISTAGG function is limited to 4,000 bytes. If you get more than this, you’ll receive an error. The most common way around this that I’ve seen is to write a custom function and use a CLOB. Watch the video to find out more and see some examples. For more information about the Oracle LISTAGG function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-listagg/
Views: 3492 Database Star
Lesson 36 Day Month Year IsDate Datepart and Datename Function in SQL in Hindi
 
07:49
In this video we will learn Day, Month, Year, IsDate, DatePart and DateName Function in SQL in Hindi
Views: 400 Vijay Mulsaniya
How to find out Highest ,Second highest Third highest salary and Nth highest salary in SQL Server
 
07:25
A Chanel Where you can learn about Computer Information and Technology Like Asp.Net C#,Asp.Net MVC,SQL Server,Bootstrap,Oracle and more Website design. Website : http://www.dawacit.com Facebook : https://www.facebook.com/dawacit05 Twitter : https://twitter.com/dawacit How to send email in ASP.NET C# using Gmail smtp server step by step https://youtu.be/rnQphXehu7g How to send email with attachment in Asp.Net through Gmail smtp server,simple steps https://youtu.be/mhRe_we4hhA How to create Navigation bar in Asp.Net with CSS https://youtu.be/xVgtAdxhIxk How to create website using ASP.NET very attractive design https://youtu.be/uAKWaCl-vK4 How to create login and logout in Asp.Net MVC https://youtu.be/7E8IK_eAsWE
Views: 39783 Dawa CIT
Power BI Tutorial: Dynamically Filter By Today's Date
 
05:50
In this Power BI Tutorial, Patrick looks at how to dynamically filter by today's date. Using a date table, you can create a DAX measure that can then be used as a Power BI report-level filter to dynamically filter by today's date within Power BI Desktop.be LET'S CONNECT! Guy in a Cube -- https://guyinacube.com -- http://twitter.com/guyinacube -- http://www.facebook.com/guyinacube -- Snapchat - guyinacube -- https://www.instagram.com/guyinacube/ ***Gear*** Check out my Tools page - https://guyinacube.com/tools/ #powerbi #guyinacube
Views: 25967 Guy in a Cube
SQL TOP, RANK, DENSE_RANK, ROW_NUMBER Functions
 
08:27
Jamie King of Neumont University demonstrating various SQL ranking functions.
Views: 24289 Jamie King
T-SQL: Running Totals
 
04:44
T-SQL: Running Totals Please check my blog azizsharif.blogspot.com for more videos!
MSSQL -  Query to group by time intervals  = group by 15 minutes time intervals
 
05:52
this simple example will group data by time intervals - in my case 15 minutes More info with ready to copy and paste query - http://howtodomssqlcsharpexcelaccess.blogspot.ca/2016/08/mssql-query-that-loads-data-by-15.html
Views: 3600 Vis Dotnet
Part 1   How to find nth highest salary in sql
 
11:45
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-1-how-to-find-nth-highest-salary_17.html This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary. By the end of this video, we will be able to answer all the following questions as well. How to find nth highest salary in SQL Server using a Sub-Query How to find nth highest salary in SQL Server using a CTE How to find the 2nd, 3rd or 15th highest salary Let's use the following Employees table for this demo Use the following script to create Employees table Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) GO To find the highest salary it is straight forward. We can simply use the Max() function as shown below. Select Max(Salary) from Employees To get the second highest salary use a sub query along with Max() function as shown below. Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees) To find nth highest salary using Sub-Query SELECT TOP 1 SALARY FROM ( SELECT DISTINCT TOP N SALARY FROM EMPLOYEES ORDER BY SALARY DESC ) RESULT ORDER BY SALARY To find nth highest salary using CTE WITH RESULT AS ( SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEES ) SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N To find 2nd highest salary we can use any of the above queries. Simple replace N with 2. Similarly, to find 3rd highest salary, simple replace N with 3. Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates. WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
Views: 1026020 kudvenkat
[SQL] Lead & Lag Window Function
 
05:45
This tutorial will walk you through SQL server on using the Lead or Lag function. Adding this to your SQL select statement, it can output you the previous/next data value in the partition. This is very useful when wanting to see a particular data value a customer had prior to making a change. Github Script: https://github.com/ProfessorPitch/ProfessorPitch/blob/master/SQL/Window%20Functions/Lead%20Lag%20Exercise.sql Lag: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017 Lead: https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017
Views: 180 ProfessorPitch
Aggregate Functions in SQL Server
 
47:37
Website:https://www.eclasess.com sum(),max(),min(),avg(),count() functions in sql server Group by ,having,order by clauses
Part 8   SQL Query to find department with highest number of employees
 
04:03
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/06/part-8-sql-query-to-find-department.html Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC
Views: 134146 kudvenkat
Oracle SQL: Understanding Multiple Table Joins
 
08:10
http://www.informit.com/store/oracle-sql-livelessons-video-training-downloadable-9780134275741?WT.mc_id=Social_YT Oracle SQL: Understanding Multiple Table Joins is an excerpt from Oracle SQL LiveLessons Video Training -- 6+ Hours of Video -- Oracle SQL LiveLessons Video Training covers the basics of the Oracle SQL programming language. This course covers both Oracle standard SQL and the ANSI approved SQL that Oracle supports. The focus of the course is to give students working knowledge of the Oracle SQL language and at the conclusion of this course, students should be able to query the Oracle database. Description In this LiveLessons video course, Oracle ACE Director Dan Hotka will teach you how to use the SQL query language, interpret business questions into SQL code, and how to use Where clauses sub-queries, complex table join conditions, and how to create table objects and populate tables with data. Students will also learn how to access Oracle from Microsoft products such as Excel. In addition, they will learn how analytical SQL can be used for business analysis. This course will use both Toad and SQL Developer for SQL examples. About the Instructor Dan Hotka is a training specialist and an Oracle ACE director who has more than 37 years in the computer industry and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 12 Oracle books and well over 200 published articles. He is also the video author for Oracle SQL Performance Tuning for Developers LiveLessons and Oracle PL/SQL Programming Fundamentals. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com. Skill Level Beginner Learn How To Use the SQL query language Interpret business questions into SQL code Understand Oracle standard SQL and ANSI SQL Work with Where clauses, sub-queries, and complex table join conditions Create table objects and populate these tables with data Access Oracle from Microsoft products such as Excel Use analytical SQL, useful for business analysts Who Should Take This Course Students who are new to SQL and want to learn Oracle SQL Course Requirements Basic knowledge of what is a database, especially RDBMS and what is a computer programming language http://www.informit.com/store/oracle-sql-livelessons-video-training-downloadable-9780134275741?WT.mc_id=Social_YT
Views: 15774 LiveLessons