solving the error - 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

solving the error

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2008-10-22 : 07:43:40

hi,

can you please let me know where am doing the mistake i am getting an error

select t0.[Code]
,sum(t0.[Q'TY])
,t0.[date]

from (
SELECT [Code]
,RIGHT(CONVERT(varchar(10),[date],105),7)as Date
,[Q'TY]

FROM table1 t1
JOIN table t2
ON t1.[VOI]=SUBSTRING(t2.[VOI],CASE WHEN CHARINDEX('/',t2.[VOI])>0 THEN CHARINDEX('/',t2.[VOI])+1 ELSE 1 END,LEN(t2.[VOI]))
) as t0
GROUP BY [DATE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 07:49:52

What is the error?

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 07:50:48

select t0.[Code]
,sum(t0.[Q'TY])
,t0.[date]

from (
SELECT [Code]
,RIGHT(CONVERT(varchar(10),[date],105),7)as [Date]
,[Q'TY]

FROM table1 t1
JOIN [table] t2
ON t1.[VOI]=SUBSTRING(t2.[VOI],CASE WHEN CHARINDEX('/',t2.[VOI])>0 THEN CHARINDEX('/',t2.[VOI])+1 ELSE 1 END,LEN(t2.[VOI]))
) as t0
GROUP BY [DATE]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 07:51:46

Try and not use keywords like date and table, if you do, fence them with []

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 08:19:48

quote:
Originally posted by sakets_2000

Try and not use keywords like date and table, if you do, fence them with []

date is not keyword until sql 2008.

Leo_Don
Starting Member

42 Posts

Posted - 2008-10-22 : 09:36:46

Hi,

Thats not working

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 09:43:15

quote:
Originally posted by Leo_Don

Hi,

Thats not working

Did you read my first reply?

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 09:43:41

quote:
Originally posted by Leo_Don

Hi,

Thats not working

whats the error ?

Leo_Don
Starting Member

42 Posts

Posted - 2008-10-22 : 09:46:34

this is the message

Message 8120, Level 16, State 1, Line 2
The 't0.Code' column in the selection list invalid because they are not in an aggregate function and not in the GROUP BY clause is included.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 09:51:53

select t0.[Code]
,sum(t0.[Q'TY])
,t0.[date]

from (
SELECT [Code]
,RIGHT(CONVERT(varchar(10),[date],105),7)as Date
,[Q'TY]

FROM table1 t1
JOIN table t2
ON t1.[VOI]=SUBSTRING(t2.[VOI],CASE WHEN CHARINDEX('/',t2.[VOI])>0 THEN CHARINDEX('/',t2.[VOI])+1 ELSE 1 END,LEN(t2.[VOI]))
) as t0
GROUP BY t0.[Code]
,t0.[date]

Madhivanan

Failing to plan is Planning to fail

Leo_Don
Starting Member

42 Posts

Posted - 2008-10-22 : 09:59:48

i want to group the table with date(month wise)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 10:09:20

select t0.[Code]
,sum(t0.[Q'TY])
,dateadd(month,datediff(month,t0.[date],0),0)

from (
SELECT [Code]
,RIGHT(CONVERT(varchar(10),[date],105),7)as Date
,[Q'TY]

FROM table1 t1
JOIN table t2
ON t1.[VOI]=SUBSTRING(t2.[VOI],CASE WHEN CHARINDEX('/',t2.[VOI])>0 THEN CHARINDEX('/',t2.[VOI])+1 ELSE 1 END,LEN(t2.[VOI]))
) as t0
GROUP BY t0.[Code]
,dateadd(month,datediff(month,t0.[date],0),0)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 10:24:22

quote:
Originally posted by Leo_Don

this is the message

Message 8120, Level 16, State 1, Line 2
The 't0.Code' column in the selection list invalid because they are not in an aggregate function and not in the GROUP BY clause is included.

select {MAX/MIN}(t0.[Code])
,sum(t0.[Q'TY])
,t0.[date]

from (
SELECT [Code]
,RIGHT(CONVERT(varchar(10),[date],105),7)as [Date]
,[Q'TY]

FROM table1 t1
JOIN [table] t2
ON t1.[VOI]=SUBSTRING(t2.[VOI],CASE WHEN CHARINDEX('/',t2.[VOI])>0 THEN CHARINDEX('/',t2.[VOI])+1 ELSE 1 END,LEN(t2.[VOI]))
) as t0 
GROUP BY [DATE]

you need to use MIN or MAX on Code or include it in GROUP BY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources