| Author |
 Topic  |
|
|
babloo
Starting Member
USA
26 Posts |
 Posted - 02/28/2013 : 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 field12
select 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 field12
Is there a way I could write one Query and that will give me results like
Date Archived Groups Total
2013-02-23 Group 1 23
2013-02-23 Group 2 50
2013-02-24 Group 1 65
2013-02-24 Group 2 78
2013-02-25 Group 1 32
2013-02-25 Group 2 14
Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/28/2013 : 10:32:48
|
whats field that contains group details?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 02/28/2013 : 10:38:53
|
| Thanks Viaskh, we will use field6 for grouping |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/28/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 02/28/2013 : 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 10462
2013-02-21 10318
2013-02-22 9510
Query 2
2013-02-20 436
2013-02-21 462
2013-02-22 467
I want:
Date Archived Groups Total
2013-02-23 Group 1 10462
2013-02-23 Group 2 436
2013-02-24 Group 1 10318
2013-02-24 Group 2 462
2013-02-25 Group 1 9510
2013-02-25 Group 2 467
Thanks.
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/28/2013 : 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 field12
UNION ALL
select 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 [Date Archived],field12 |
|
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 02/28/2013 : 11:47:47
|
| Thanks James. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/28/2013 : 12:01:33
|
| You are very welcome - glad to help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/28/2013 : 12:55:54
|
why do you need two selects here?
I think you need just this
select 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'
end
order by [Date Archived],GroupName
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
babloo
Starting Member
USA
26 Posts |
Posted - 03/01/2013 : 08:22:34
|
| That's absolutely what I want Thanks alot Visakh. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 03/01/2013 : 08:55:10
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Help with Grouping together
