Thursday, February 13, 2020

How to use Count(*) twice in one sql statement

Using SQL Count() function twice in a Select statement, we can achieve below ways

Consider in general ways we need to find the product names and corresponding Male or Female subscriber to that product. 


We can use better example also, I have used here the functionality of usage.


select 

dm.EnglishProductName,
count(dm.EnglishProductName) over (Partition by dm.EnglishProductName) CntProduct ,
dc.Gender,count(dc.Gender) over (Partition by dc.Gender,dm.EnglishProductName) CntProduct
from [dbo].[FactInternetSales] FI
join DimProduct DM on DM.ProductKey=Fi.ProductKey
join DimCustomer DC on dc.CustomerKey=fi.CustomerKey
where dm.EnglishProductName in('Long-Sleeve Logo Jersey, S','All-Purpose Bike Stand')


output :



Above scipt will display count of both Product as well Gender
If it required to display count of distinct Gender for the product details, we do below way

SELECT dm.EnglishProductName 
, SUM(CASE WHEN dc.Gender='M' THEN 1 ELSE 0 END) AS 'Male'
, SUM(CASE WHEN dc.Gender='F' THEN 1 ELSE 0 END) AS 'Female'
from [dbo].[FactInternetSales] FI
join DimProduct DM on DM.ProductKey=Fi.ProductKey
join DimCustomer DC on dc.CustomerKey=fi.CustomerKey
where dm.EnglishProductName in('Long-Sleeve Logo Jersey, S','All-Purpose Bike Stand') 
GROUP BY dm.EnglishProductName

Output:



Monday, February 10, 2020

The value of the Color property for the textrun which is not a valid Color in SSRS

While setting up background color of a tablix cell or font, below error is generated.

The value of the Color property for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ is “#00ffffff” which is not a valid Color.

Textbox does not support (partial) transparency color.


Need to select valid RGB color values. (instead of selecting " No Color")