Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjcclee
Starting Member
33 Posts |
 Posted - 2013-08-16 : 13:30:56
|
| Hi, all, Is there better way to do this instead of use seperate group by and count(*), then union each query? For example Date attribute1 attribute2 attribute3 2013/08/01 value1 type1 something1 2013/07/01 value2 type2 somthing2the final result want to be: count each year and month how many value1, value2, type1 type2 etc Year Month Value1 value2 type1 type2 something1 something2 Grandtotal Is better way to do this intead of different group by and count and union all queries? Thank you! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-16 : 14:03:34
|
| [code]SELECT YEAR([Date]) AS YrVal,MONTH([Date]) AS MonthVal,MAX(CASE WHEN attribute2 = 'type1' THEN attribute1 END) AS value1,MAX(CASE WHEN attribute2 = 'type2' THEN attribute1 END) AS value2,MAX(CASE WHEN attribute2 = 'type1' THEN attribute2 END) AS type1,MAX(CASE WHEN attribute2 = 'type2' THEN attribute2 END) AS type2,MAX(CASE WHEN attribute2 = 'type1' THEN attribute3 END) AS something1,MAX(CASE WHEN attribute2 = 'type2' THEN attribute3 END) AS something2FROM TableGROUP BY YEAR([Date]),MONTH([Date])[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-16 : 14:08:22
|
| You can use ROLLUP or GROUPING SETS as described here:http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx |
|
|
|
cjcclee
Starting Member
33 Posts |
Posted - 2013-08-16 : 15:12:48
|
| Thank you so much! It is nuch simply the code! |
|
|
|
|
|
|
|
|
group by and count(*)