Jatin Jatin - 2 months ago 6
MySQL Question

create seperate column from one row based on values

I have a column named Source which has 2 values "1" and "2".
I want to display it in 2 columns like Source_1 which will display just value "1" and Source_2 which will display value "2".
I am not sure how do I achieve it given the query that I have.

Select
b.[path],
count(*) as "No of Calls",
count(a.Source) as "Source_1 calls",
count(a.Source) as "Source_2 calls",
a.TimeDataRetrieval as "DB Retrieval time",
a.TimeProcessing as "Processing time",
a.TimeRendering as "Rendering Time"

FROM LogStorage a inner join Catalog b on a.[ReportID] = b.[ItemID]

where b.[path] = ('/../some_path') and a.[Source]=2
group by b.[path]


Here's where I am stuck. What should I do in where clause, coz I can't have a.[Source]=1 and 2

Thanks

Answer

Use Conditional Aggregate

Select 
b.[path],
count(*) as "No of Calls",
count(case when a.Source=1 then 1 end) as "Source_1 calls",
count(case when a.Source=2 then 1 end) as "Source_2 calls",
a.TimeDataRetrieval as "DB Retrieval time",
a.TimeProcessing as "Processing time",
a.TimeRendering as "Rendering Time"
FROM LogStorage a inner join Catalog b on a.[ReportID] = b.[ItemID]

where b.[path] = ('/../some_path')  and a.[Source]=2
group by b.[path]

or simply

sum(a.Source=1) as "Source_1 calls",
sum(a.Source=2) as "Source_2 calls",