TSql Practical SQL Examples (Aggregates)

 

This is the 4th post on practical T-SQL; this post focuses on joining tables this time to continue the first one on SELECT, where you would start with…

Aggregates

The aggregation helps to do the math on our oltp data…

 

SELECT
COUNT(*)
FROM dbo.DimProduct

———–

606

N> use COUNT_BIG is the expected results is bigint value

 

SELECT
COUNT(ProductSubcategoryKey)
FROM dbo.DimProduct

–397

–Warning: Null value is eliminated by an aggregate or other SET operation.

N> counting on same column could give less resultbeacuse of some NULL values, but you can have evene repertiotions of the same value because the row part of the result could be differnet on other columns even with the same ProductSubcategoryKey

SELECT
COUNT(DISTINCT ProductSubcategoryKey)
FROM dbo.DimProduct

–37

–Warning: Null value is eliminated by an aggregate or other SET operation.

Use the DISTINCT keyword to avoid duplication in the results

 

SELECT
MIN(EnglishProductName),
MAX(ListPrice)
FROM dbo.DimProduct

————————————————– ———————

Adjustable Race 3578.27

You can use MIN,MAX on varchar and money with different menaing (alfabetical order,a nd numeric order), but you cannot do the same with SUM

N> You cannot do this SELECT MIN(EnglishProductName), (ListPrice) FROM dbo.DimProduct, see later why

SELECT
‘$$$ ‘+
CAST(
cast(SUM(SalesAmount)
as
decimal(17,2))
AS
VARCHAR)
AS [Total sales] FROM dbo.FactResellerSales

–Total sales

———————————

–$$$ 80450596.98

 

SELECT
‘$$$’+
SUM(SalesAmount)
AS [Total sales] FROM dbo.FactResellerSales

–Total sales

———————–

–Msg 235, Level 16, State 0, Line 1

–Cannot convert a char value to money. The char value has incorrect syntax.

You need to cast to when mixing varchar and numeric values

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s