Showing posts with label Statistics. Show all posts
Showing posts with label Statistics. Show all posts

Monday, 14 November 2016

SQL SERVER MIN FORMULA

SQL SERVER MIN FORMULA

--- THEORY

-- SELECT MIN(ColumnName) AS [ALIAS NAME]

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME];

--- EXAMPLE

SELECT MIN(EnglishProductSubcategoryName) AS [DISPLAY MINIMUM OR LOWEST TEXT VALUE]
FROM AdventureWorksDW2012.DBO.DimProductSubcategory;

SQL SERVER MAX FORMULA

SQL SERVER MAX FORMULA

--- THEORY

-- SELECT MAX(ColumnName) AS [ALIAS NAME]

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME];

--- EXAMPLE

SELECT MAX(EnglishProductSubcategoryName) AS [DISPLAY MAXIMUM OR HIGHEST TEXT VALUE]
FROM AdventureWorksDW2012.DBO.DimProductSubcategory;

SQL SERVER LEAD FORMULA

SQL SERVER LEAD FORMULA

-- THEORY

-- SELECT [COLUMN ID KEY], [DATE AS YEAR FORMAT] AS [ALIAS COLUMN NAME], [AMOUNT IN MONEY],

-- LEAD([AMOUNT IN MONEY],1,0) OVER (ORDER BY [DATE AS YEAR FORMAT]) AS [ALIAS COLUMN NAME]

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME] AS [TABLE ALIAS NAME]

-- WHERE [TABLE ALIAS NAME].[DATE AS YEAR FORMAT] IS NOT NULL;

-- EXAMPLE

SELECT RESELLER.ResellerKey, RESELLER.FirstOrderYear AS FIRSTYEAR, RESELLER.AnnualRevenue,
LEAD(RESELLER.AnnualRevenue,1,0) OVER (ORDER BY RESELLER.FIRSTORDERYEAR) AS ORDER_YEAR_NEXT_AMOUNT
FROM AdventureWorksDW2012.dbO.DimReseller AS RESELLER
WHERE RESELLER.FirstOrderYear IS NOT NULL;

SQL SERVER LAST VALUE FORMULA

SQL SERVER LAST VALUE FORMULA

--THEORY

-- SELECT [COLUMN ID KEY], [YEAR COLUMN] AS [ALIAS COLUMN NAME], [MONEY COLUMN],

-- LAST_VALUE([YEAR COLUMN]) OVER (PARTITION BY [MONEY COLUMN] ORDER BY [YEAR COLUMN]) AS LAST_VALUE

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME] AS [TABLE ALIAS NAME]

-- WHERE [TABLE ALIAS NAME].[YEAR DATE COLUMN] IS NOT NULL;

-- EXAMPLE

SELECT RESELLER.ResellerKey, RESELLER.FirstOrderYear AS FIRSTYEAR, RESELLER.AnnualRevenue,
LAST_VALUE(RESELLER.FirstOrderYear) OVER (PARTITION BY RESELLER.ANNUALREVENUE ORDER BY RESELLER.FIRSTORDERYEAR) AS LAST_VALUE
FROM AdventureWorksDW2012.dbO.DimReseller AS RESELLER
WHERE RESELLER.FirstOrderYear IS NOT NULL;

SQL SERVER FLOOR FORMULA

SQL SERVER FLOOR FORMULA

-- THIS FINDS THE LOWEST WHOLE CLOSEST INTEGER TO A DECIMAL NUMBER FOR EXAMPLE

-- THEORY

-- SELECT FLOOR(ENTER DECIMAL NUMBER)

-- EXAMPLE

SELECT FLOOR(24.79);

-- THE VALUE IS 24

SQL Server First Value Function

-- SQL Server First Value Function

-- THE FOLLOWING IS A FIRST VALUE FUNCTION USING OVER AND ORDER BY INCLUDING GROUP BY

-- THEORY:

/* SELECT COL1 ,FIRST_VALUE(COL2) OVER (ORDER BY COL1) AS FIRSTVALUE
FROM dATABASEnAME.DBO.TABLENAME AS ALIAS
GROUP BY COL2 , COL1, COL3; */

-- EXAMPLE:

SELECT ACC.AccountType ,FIRST_VALUE(ACC.AccountDescription) OVER (ORDER BY ACC.AccounTTYPE) AS FIRSTVALUE
FROM AdventureWorksDW2012.dbo.DimAccount AS ACC
GROUP BY ACC.AccountDescription ,ACC.AccountType, ACC.AccountKey;

-- EXECUTE (FN F5)

SQL SERVER DIFFERENCE FORMULA

SQL SERVER DIFFERENCE FORMULA

-- HI VIEWERS

-- TODAY WE ARE LOOKING AT THE DIFFERENCE FORMULA

-- START OFF BY INSERTING THE SELECT KEYWORD FOLLOWED BY THE FUNCTION NAME

-- THEORY

-- SELECT DIFFERENCE('VALUE 1', 'VALUE 2');

-- PRACTICAL

SELECT DIFFERENCE('VALUE 1','VALUE 2');

SELECT DIFFERENCE ('VALUE 1','VAL 2');

SELECT DIFFERENCE('VALUE 1',2);

SELECT DIFFERENCE('VALUE 1','2')

-- THAT IS IT

SQL SERVER FUNCTION COUNT

SQL SERVER FUNCTION COUNT

/*COUNT FUNCTION SQL SERVER
MANAGEMENT STUDIO*/

--HI VIEWERS, TODAY WE ARE LOOKING AT THE COUNT FUNCTION IN SQL SERVER MANAGEMENT STUDIO

--WE WILL COUNT THE NUMBER OF ROWS IN A TABLE

--FIRST WE START WITH ENTERING THE KEYWORD, SELECT, THEN THE FUNCTION NAME COUNT(

SELECT COUNT(*)
FROM DBO.DimProduct;

--SO THE THEORY IS AS SO

-- SELECT COUNT(ALL ROWS IN TABLE(S))

-- FROM TABLE NAME;

--NOW HIGHLIGHT THE CODE AND CLICK EXECUTE OR (F5)

-- AS YOU CAN SEE FROM THE RESULT BELOW, THERE ARE 606 RECORDS IN THE PRODUCT TABLE

SQL SERVER COUNT BIG FUNCTION

SQL SERVER COUNT BIG FUNCTION

/* COUNT_BIG FUNCTION IN SQL SERVER SSMS */

--ALRIGHT, WE WILL BE USING THE COUNT_BIG FUNCTION IN THIS TUTORIAL

--FIRST WE START BY ENTERING THE KEYWORD, SELECT, THEN THE FUNCTION NAME

--COUNT_BIG TO COUNT_BIG ALL THE RECORDS IN A TABLE

SELECT COUNT_BIG(*)

FROM DimAccount;

--THE THEORY WORKS LIKE THIS

-- SELECT COUNT_BIG(ALL ROWS IN TABLE(S)

-- FROM TABLE NAME

-- SO SELECT THE CODE AND CLICK EXECUTE OR (F5)

-- AS YOU CAN SEE THE TOTAL NUMBER OF ROWS IN THE ACCOUNT TABLE IS 99

-- THAT IS IT

SQL SERVER CEILING FORMULA

SQL SERVER CEILING FORMULA

--alright viewers, today we will be looking at the Ceiling function in SQL SERVER SSMS

--the ceiling function works a bit like the round function but is different.

--the ceiling function takes any number with decimal places and rounds it up to the next higher value.

--it doesn't matter if the value is below 5 or not.

--I will now demonstrate

select CEILING(101.0000001);

--let us look at this again using listprice in the product table

select CEILING(P.ListPrice) as [Ceiling Function], ListPrice
from AdventureWorksDW2012.dbo.DimProduct as P;

--you can see from the comparison of the ceiling function values and the normal values that

--no matter what the decimal value is the numbers have been rounded up to the next higher whole value

--despite if the decimal value is >= 5 or not.

--that is it.

SQL SERVER AVG AVERAGE AGGREGATE FORMULA

SQL SERVER AVG AVERAGE AGGREGATE FORMULA

--alright viewers, today we will look at the average list price in the products table

--we are using SQL Server 2012 in SSMS

--let us begin

select AVG(P.ListPrice)
from AdventureWorksDW2012.dbo.DimProduct as P;

--as you can see the average list price in the product table is 747.6617.

--now let's have a look at what the list price values are for a unique set of list prices

--distinct means select a unique list price

select distinct P.ListPrice
from AdventureWorksDW2012.dbo.DimProduct as P;

--that is it.

Monday, 7 November 2016

SQL SERVER AGREGATES

SQL SERVER AGREGATES


SELECT MIN(ANNUALREVENUE) MINIMUM,MAX(ANNUALREVENUE) MAXIMUM, AVG(ANNUALREVENUE) AVERAGE,SUM(ANNUALREVENUE) TOTAL


FROM AdventureWorksDW2012.DBO.DimReseller;


Figure 3 aggregates