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
 General SQL Server Forums
 New to SQL Server Programming
 Grouping the OEM and Month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mahesh.sanka
Starting Member

India
18 Posts

Posted - 05/23/2013 :  02:09:24  Show Profile  Reply with Quote

Hi,

I have one table where i am storing the oem details along with the values month wise

OEM(varchar) month(varchar) value(numeric)
Avaya Mar-2013 20.65
Cisco Feb-2013 0
others May-2013 8.02
Avaya Feb-2013 0
others Feb-2013 10.92
Avaya Apr-2013 52.94
Cisco Mar-2013 25.61
Vernit Mar-2013 19.61
others Mar-2013 25.61
Cisco Apr-2013 52.94
others Apr-2013 52.94

To group the results , i have used the following query

select oem,MONTH,value from OEM_MAIN group by OEM,MONTH,value order by OEM,month

The result i am getting is
Avaya Mar-2013 20.65
Avaya Feb-2013 0
Avaya Apr-2013 52.94
Cisco Mar-2013 25.61
Cisco Feb-2013 0
Cisco Apr-2013 52.94
others May-2013 8.02
others Mar-2013 25.61
others Feb-2013 10.92
others Apr-2013 52.94
Vernit Mar-2013 19.61

But i want the o/p like

Avaya Feb-2013 0
Avaya Mar-2013 20.65
Avaya Apr-2013 52.94
Cisco Feb-2013 0
Cisco Mar-2013 25.61
Cisco Apr-2013 52.94
others Feb-2013 10.92
others Mar-2013 25.61
others Apr-2013 52.94
others May-2013 8.02
Vernit Mar-2013 19.61

what changes i need to make in the query to get above desired result

Thanks & Regards,
Mahesh Kumar Sanka

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/23/2013 :  02:14:31  Show Profile  Reply with Quote
you need to do like this

select oem,MONTH,value 
from OEM_MAIN
order by OEM,'01-' + [MONTH] 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/23/2013 :  02:15:13  Show Profile  Reply with Quote
Forgot to add, I dont think you need the GROUP By as you already have data in the same level you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts

Posted - 05/23/2013 :  02:27:07  Show Profile  Reply with Quote
Hi,

Thanks for ur reply. I have used the below mentioned query but i am not getting desired results. Even i have removed the group by condition

quote:
Originally posted by visakh16

you need to do like this

select oem,MONTH,value 
from OEM_MAIN
order by OEM,'01-' + [MONTH] 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/23/2013 :  02:28:02  Show Profile  Reply with Quote
what about this?

select oem,MONTH,value 
from OEM_MAIN
order by OEM,CAST('01-' + [MONTH]  AS datetime)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/23/2013 :  02:34:30  Show Profile  Reply with Quote
here's the full illustration


declare @test table
(
OEM varchar(30),
month varchar(20),
 value numeric(10,2)
)
insert @test
select 'Avaya','Mar-2013', 20.65 union all
select 'Cisco','Feb-2013' ,0 union all
select 'others','May-2013' ,8.02 union all
select 'Avaya','Feb-2013' ,0 union all
select 'others','Feb-2013' ,10.92 union all
select 'Avaya','Apr-2013' ,52.94 union all
select 'Cisco','Mar-2013' ,25.61 union all
select 'Vernit','Mar-2013' ,19.61 union all
select 'others','Mar-2013' ,25.61 union all
select 'Cisco','Apr-2013' ,52.94 union all
select 'others','Apr-2013' ,52.94 


select oem,MONTH,value 
from @test
order by OEM,CAST('01-' + [MONTH]  AS datetime)



output
------------------------------
oem	MONTH	        value
------------------------------
Avaya	Feb-2013	0.00
Avaya	Mar-2013	20.65
Avaya	Apr-2013	52.94
Cisco	Feb-2013	0.00
Cisco	Mar-2013	25.61
Cisco	Apr-2013	52.94
others	Feb-2013	10.92
others	Mar-2013	25.61
others	Apr-2013	52.94
others	May-2013	8.02
Vernit	Mar-2013	19.61



hope this explains to you why its importatnt to use proper datatype for your fields. Retaining date values as dates would have saved you lot of this unwanted casting effort

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mahesh.sanka
Starting Member

India
18 Posts

Posted - 05/23/2013 :  02:35:18  Show Profile  Reply with Quote
Hi,

Thank you very much. Problem has solved.
quote:
Originally posted by visakh16

what about this?

select oem,MONTH,value 
from OEM_MAIN
order by OEM,CAST('01-' + [MONTH]  AS datetime)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks & Regards,
Mahesh Kumar Sanka
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/23/2013 :  02:35:53  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000