SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with Grouping together
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

babloo
Starting Member

USA
35 Posts

Posted - 02/28/2013 :  10:28:42  Show Profile  Reply with Quote
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
52309 Posts

Posted - 02/28/2013 :  10:32:48  Show Profile  Reply with Quote
whats field that contains group details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 02/28/2013 :  10:38:53  Show Profile  Reply with Quote
Thanks Viaskh, we will use field6 for grouping
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/28/2013 :  10:41:17  Show Profile  Reply with Quote
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/

Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 02/28/2013 :  11:19:48  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 02/28/2013 :  11:37:00  Show Profile  Reply with Quote
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
Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 02/28/2013 :  11:47:47  Show Profile  Reply with Quote
Thanks James.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 02/28/2013 :  12:01:33  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/28/2013 :  12:55:54  Show Profile  Reply with Quote
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/

Go to Top of Page

babloo
Starting Member

USA
35 Posts

Posted - 03/01/2013 :  08:22:34  Show Profile  Reply with Quote
That's absolutely what I want Thanks alot Visakh.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/01/2013 :  08:55:10  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000