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 |
babloo
Starting Member
35 Posts |
Posted - 2013-02-28 : 10:28:42
|
Hi I have these queries which I run seperateley for two different groups of field6. I was wondering if I could compile these queries into one and add additional column 'Group'select field12 as [Date Archived], count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5') group by field12 order by field12select field12 as [Date Archived], count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeB1', 'DoctypeB2', 'DoctypeB3', 'DoctypeB4', 'DoctypeB5') group by field12 order by field12Is there a way I could write one Query and that will give me results like Date Archived Groups Total2013-02-23 Group 1 232013-02-23 Group 2 502013-02-24 Group 1 652013-02-24 Group 2 782013-02-25 Group 1 322013-02-25 Group 2 14Thank you |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 10:32:48
|
whats field that contains group details?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-02-28 : 10:38:53
|
Thanks Viaskh, we will use field6 for grouping |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 10:41:17
|
do you mean this?select field12 as [Date Archived],field6 , count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5')group by field12,field6 order by field12,field6 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-02-28 : 11:19:48
|
No, I want to add another Column in that Query and define Groups for each set of field6 meaning DoctypeA1 - DoctypeA5 should be grouped as Group1 and DoctypeB1 - B5 should be grouped as Group2.. kinda like Case logic.Currently I am getting these results:Query 1.2013-02-20 104622013-02-21 103182013-02-22 9510Query 22013-02-20 4362013-02-21 4622013-02-22 467I want:Date Archived Groups Total2013-02-23 Group 1 104622013-02-23 Group 2 4362013-02-24 Group 1 103182013-02-24 Group 2 4622013-02-25 Group 1 95102013-02-25 Group 2 467Thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 11:37:00
|
Use UNION ALL to combine the result sets:select field12 as [Date Archived], count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5')group by field12--order by field12UNION ALLselect field12 as [Date Archived], count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeB1', 'DoctypeB2', 'DoctypeB3', 'DoctypeB4', 'DoctypeB5')group by field12order by [Date Archived],field12 |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-02-28 : 11:47:47
|
Thanks James. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 12:01:33
|
You are very welcome - glad to help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 12:55:54
|
why do you need two selects here?I think you need just thisselect field12 as [Date Archived], case when field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5') then 'Group 1'when field6 IN ('DoctypeB1', 'DoctypeB2', 'DoctypeB3', 'DoctypeB4', 'DoctypeB5') then 'Group 2'end as GroupName,count(*) Total from ae_dt1 where field9 >= '2013-02-23 00:00:00' and field8 = 'COMPLETE' and field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5','DoctypeB1', 'DoctypeB2', 'DoctypeB3', 'DoctypeB4', 'DoctypeB5')group by field12,case when field6 IN ('DoctypeA1', 'DoctypeA2', 'DoctypeA3', 'DoctypeA4', 'DoctypeA5') then 'Group 1'when field6 IN ('DoctypeB1', 'DoctypeB2', 'DoctypeB3', 'DoctypeB4', 'DoctypeB5') then 'Group 2'endorder by [Date Archived],GroupName ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-03-01 : 08:22:34
|
That's absolutely what I want Thanks alot Visakh. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 08:55:10
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|