August 19, 2013 at 10:43 am
Hi all
Suppose we have a table DATHIS with the following information:
[X1]---------[X2]-[X3]
2013-08-10, 1250, 75.0
2013-08-10, 1251, 82.0
2013-08-10, 1252, 35.0
2013-08-10, 1253, 45.0
2013-08-11, 1250, 75.1
2013-08-11, 1251, 82.2
2013-08-11, 1252, 50.3
2013-08-11, 1253, 50.4
2013-08-12, 1250, 75.5
2013-08-12, 1251, 82.6
2013-08-12, 1252, 45.7
2013-08-12, 1253, 55.8
We need the output like this:
[Y1]--------[Y2]--[Y3]-[Y4]
2013-08-10, 75.0, 82.0, 0
2013-08-11, 75.1, 82.2, 2
2013-08-12, 75.5, 82.6, 1
On Y4 column we need to count the number of X2 IN (1252, 1253) >= 50.
We did the following SQL command for Y1, Y2 and Y3:
SELECT X1 Y1, [1250] Y2, [1251] Y3 FROM
(SELECT X1, X2, X3 FROM DATHIS WHERE X1>='2013-08-10') H
PIVOT ( SUM(X3) FOR X2 in ([1250], [1251]) ) AS DH
ORDER BY X1
But, now how can we include Y4, for counting of number of rows >= 50.0 for X2 IN (1252, 1253)?
Any one have an idea for that, please?
Best Regards
Paulo
:hehe: :crazy:
August 19, 2013 at 10:57 am
Applying cross tabs with different aggregations it's quite easy.
Read the following articles to find more information on cross tabs
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
and read the article linked on my signature to follow best practices when posting sample data for your next posts.
Here's the code with the sample data in a CTE to test.
WITH SampleData(X1, X2, X3) AS(
SELECT '2013-08-10', 1250, 75.0 UNION ALL SELECT
'2013-08-10', 1251, 82.0 UNION ALL SELECT
'2013-08-10', 1252, 35.0 UNION ALL SELECT
'2013-08-10', 1253, 45.0 UNION ALL SELECT
'2013-08-11', 1250, 75.1 UNION ALL SELECT
'2013-08-11', 1251, 82.2 UNION ALL SELECT
'2013-08-11', 1252, 50.3 UNION ALL SELECT
'2013-08-11', 1253, 50.4 UNION ALL SELECT
'2013-08-12', 1250, 75.5 UNION ALL SELECT
'2013-08-12', 1251, 82.6 UNION ALL SELECT
'2013-08-12', 1252, 45.7 UNION ALL SELECT
'2013-08-12', 1253, 55.8)
SELECT X1 AS Y1,
MAX(CASE WHEN X2 = 1250 THEN X3 END) AS Y2,
MAX(CASE WHEN X2 = 1251 THEN X3 END) AS Y3,
SUM(CASE WHEN X2 IN( 1252, 1253) AND X3 >= 50 THEN 1 ELSE 0 END) AS Y4
FROM SampleData
GROUP BY X1
August 20, 2013 at 2:27 am
The url you give was excellent for understand and give the choice of way for my solutions.
:w00t:
😎
August 20, 2013 at 8:45 am
I'm glad it helped you to learn more 🙂 Although all credit should go to Jeff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply