Group by and order by date problem

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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by and order by date problem

Author

Topic

jipo
Starting Member

4 Posts

Posted - 2009-01-05 : 12:41:41

Hi, pals. I had a table, the structure like this

tanggal (datetime)
income (integer)

the table rows example :

tanggal                           income
12/8/2008 12:45:04 PM             5000
12/8/2008 1:49:23 PM              10000
12/9/2008 1:20:13 PM              3000

I want to displayed like this (group by date only and order by date DESC):

tanggal                 income
12/9/2008               3000
12/8/2008               15000

I used this query :
SELECT CONVERT(varchar, Tanggal, 103) AS Tanggal, SUM(income)
FROM OrderMaster
GROUP BY CONVERT(varchar, Tanggal, 103)
ORDER BY Tanggal DESC

It couldn't order "tanggal" like date format

Help me please, thanks.

Sorry for my bad english

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:45:52

[code]
SELECT DATEADD(dd,DATEDIFF(dd,0,tanggal),0) AS date,
SUM(income) AS income
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tanggal),0)
ORDER BY tanggal DESC
[/code]

jipo
Starting Member

4 Posts

Posted - 2009-01-05 : 12:55:34

wow, great.
Thanks a lot visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:57:52

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 01:05:36

Try This Also,

declare @temp table ( tanggal datetime,income int )
insert into @temp
select '12/8/2008 12:45:04 PM',5000 union all
select '12/8/2008 1:49:23 PM', 10000 union all
select '12/9/2008 1:20:13 PM', 3000

select CONVERT(VARCHAR(32),tanggal,106) AS tanggal ,sum(income) as income from @temp group by CONVERT(VARCHAR(32),tanggal,106)
order by tanggal desc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-06 : 01:25:36

quote:
Originally posted by Nageswar9

Try This Also,

declare @temp table ( tanggal datetime,income int )
insert into @temp
select '12/8/2008 12:45:04 PM',5000 union all
select '12/8/2008 1:49:23 PM', 10000 union all
select '12/9/2008 1:20:13 PM', 3000

select CONVERT(VARCHAR(32),tanggal,106) AS tanggal ,sum(income) as income from @temp group by CONVERT(VARCHAR(32),tanggal,106)
order by tanggal desc

Dont convert DATEs to VARCHARs. Vishak showed the correct way of doing it. Learn from the answers posted

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 01:44:39

May Be Like That...

select left(tanggal,10) as date ,sum(income) as income from test14 group by left(tanggal,10) order by left(tanggal,10) desc

Vishak Sir And madhi Sir if this is the right way to do it,,,,
I M learning Sql dese dayz by seeing postings there in this forums so there is lots to learn from u,,,so guide me when i did some thing wrong,,
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-06 : 03:09:03

quote:
Originally posted by ashishashish

May Be Like That...

select left(tanggal,10) as date ,sum(income) as income from test14 group by left(tanggal,10) order by left(tanggal,10) desc

Vishak Sir And madhi Sir if this is the right way to do it,,,,
I M learning Sql dese dayz by seeing postings there in this forums so there is lots to learn from u,,,so guide me when i did some thing wrong,,
Thanks

As I said in my previous reply, dont make dates to varchars
Formation should be done at front end application

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 03:30:49

see the difference,


select CONVERT(VARCHAR(32),tanggal,106) AS tanggal ,sum(income) as income 
from @temp 
group by CONVERT(VARCHAR(32),tanggal,106)
order by tanggal desc


SELECT DATEADD(dd,DATEDIFF(dd,0,tanggal),0) AS date,
SUM(income) AS income
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tanggal),0)
ORDER BY tanggal DESC

the first case orders it as if its a string while what you want is date based ordering

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 04:54:09

Thanks For This,,,,,
I get The Thing....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 08:45:04

Cheers

Nagesh1954
Starting Member

3 Posts

Posted - 2009-03-30 : 17:56:46

Hi

I am a newbee to SQL Server 2005
I have created a table with the above data
and tried to run the query answered by Vishakhi
but I am getting an error as under when I run the query

Msg 8127, Level 16, State 1, Line 1
Column "Table_1.tanggal" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Please correct me where have I commited a mistake

thanks
Nagesh1954

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-31 : 03:08:15

SELECT DATEADD(dd,DATEDIFF(dd,0,tanggal),0) AS tanggal,
SUM(income) AS income
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tanggal),0)
ORDER BY tanggal DESC

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 03:11:40

or see http://weblogs.sqlteam.com/peterl/archive/2009/03/18/A-general-approach-to-sort-different-dateformats-correctly.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources