Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Calculate Percentage in CrossTab
sjayavel
Hi ,
I have a table with the following rows.
+
+
+
+
| date | sessionParameter | count |
+
+
+
+
| 2012-06-17 | SessionCount | 1245 |
| 2012-06-16 | SessionCount | 1245 |
| 2012-06-15 | SessionCount | 1245 |
| 2012-06-14 | SessionCount | 1245 |
| 2012-06-13 | SessionCount | 1245 |
| 2012-06-19 | EscalationCount | 123 |
| 2012-06-18 | EscalationCount | 234 |
| 2012-06-17 | EscalationCount | 245 |
| 2012-06-16 | EscalationCount | 345 |
| 2012-06-15 | EscalationCount | 178 |
+
+
+
+
I am trying to Produce a Crosstab report as shown below..
06/13/12 06/14/12 06/15/12 06/16/12 06/17/12
Resolution Rate 20.00% 15.00% 20.00% 18.00% 20.00%
Where Resoultion rate is calculated as follows -(EscalationCount/sessioncount) * 100
Can someone help me how can i get this done ?
Find more posts tagged with
Comments
CBR
You should prepare the data accordingly. Is the data coming from an SQL database? If yes you should already group the data because it is much faster in the database.
Building the crosstab would be easy if you had sessioncount and escalationcount in two different columns instead of rows. My idea would be a self join on the table like
SELECT ...
WHERE res='Escalation
GROUP BY ...
FULL OUTER JOIN
SELECT ...
WHERE res='sessioncount'
GROUP BY ...
sjayavel
<blockquote class='ipsBlockquote' data-author="'cbrell'" data-cid="104601" data-time="1340097113" data-date="19 June 2012 - 02:11 AM"><p>
You should prepare the data accordingly. Is the data coming from an SQL database? If yes you should already group the data because it is much faster in the database.<br />
Building the crosstab would be easy if you had sessioncount and escalationcount in two different columns instead of rows. My idea would be a self join on the table like<br />
<br />
SELECT ...<br />
WHERE res='Escalation<br />
GROUP BY ...<br />
FULL OUTER JOIN<br />
SELECT ...<br />
WHERE res='sessioncount'<br />
GROUP BY ...<br /></p></blockquote>
<br />
<br />
Yes the data is in a Mysql database . Unfortunately i cant have both the counts as columns since this data would come from a Hadoop filesystem whose file structure matches with the table . I didnt get your Self join idea though.
Hans_vd
The idea is that you write an SQL that transforms the rows into columns.<br />
You could go with the self join construct of cbrell or try another option like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT date,
SUM (SessionCount) AS SessionCount,
SUM (EscalationCount) AS EscalationCount
FROM (SELECT date,
CASE WHEN sessionParameter = 'SessionCount' THEN count ELSE 0 END AS SessionCount,
CASE WHEN sessionParameter = 'EscalationCount' THEN count ELSE 0 END AS EscalationCount
FROM test
)
GROUP BY date</pre>
