Grouping the OEM and Month - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping the OEM and Month

Author

Topic

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-05-23 : 02:09:24

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

52326 Posts

Posted - 2013-05-23 : 02:14:31

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:15:13

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

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-05-23 : 02:27:07

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:28:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:34:30

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

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-05-23 : 02:35:18

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:35:53

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources