• SIX SQL Queries

–1.Select all columns from one table (not best practice)

SELECT *

FROM dbo.DimDate AS D;

–2.Select top 10 from one table

SELECT TOP 10 *

FROM dbo.DimDate AS D

ORDER BY D.DateKey;

–3.Select two columns in one table

SELECT DISTINCT

CalendarYear,

MonthNumberOfYear

FROM dbo.DimDate AS D;

–4.Select two columns and doing aggreation in the same table

SELECT CalendarYear,

MonthNumberOfYear,

COUNT(WeekNumberOfYear) AS countofWeeks

FROM dbo.DimDate AS D

GROUP BY CalendarYear,

MonthNumberOfYear;

–5.Select columns and aggregation in two tables

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear;

–6.Select columns and aggregation in two tables and filter out blank value

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear

HAVING SUM(F.SalesAmount) IS NOT NULL;

• Corresponding Version Queries

–1.Select all columns from one table

Evaluate

DimDate

–2.Select top 10 all columns from one table

Evaluate

TOPN(10, DimDate,DimDate[DateKey])

–3.Select two(or few) columns from one table

EVALUATE

SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] )

–4.Select two columns and doing aggreation in the same table 3ms — you can use rollup syntax inside Summarize — summarize useful only select columns in single table

EVALUATE

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear],

“countofweeks”, COUNT ( Dimdate[WeekNumberOfYear] )

)

–or which performance better 2ms — also in Excel 2013 SSAS tabular 2016 PowerBI there is a better version SELECTCOLUMNS

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“countofweeks”, CALCULATE(COUNT ( Dimdate[WeekNumberOfYear] ))

)

–5.Select columns and aggregation in two tables

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE(sum(FactInternetSales[SalesAmount] ))

)

–6.Select columns and aggregation in two tables and filter out blank value

EVALUATE

FILTER (

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )

),

NOT ( ISBLANK ( [TotalSales] ) )

)