Query help - 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

Query help

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2011-01-27 : 08:47:55

Hi Experts,

Need you help

have two tables
table 1:
ID - 1,2 , 3,4

table 2:
Action - a, a,a, b, b,b, c,c,c,d
action created date- 21-01-11, 20-01-11, 10-01-11......

i want the o/p for the sum of action of the id, where the user can select the date, i.e i want to add the action date in the selec statment...

i have written this query but i cannot add action date in the query

select distinct table_id, table2_Action, COUNT(table_Action) as 'Sum'
--table2_CreatedDate

from table1 LEFT OUTER JOIN table2 ON table2_id = Table1_id

where table2_call = 'Y'
group by table1_id, table2_action

i get this error
is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause, when i select table2_actionCreatedDate

thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 09:34:33

either
createddate = max(table2.createddate)
or
createddate = min(table2.createddate)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Leo_Don
Starting Member

42 Posts

Posted - 2011-01-27 : 09:39:38

sorry i dint get you?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 09:43:09

select distinct table_id, table2_Action, COUNT(table_Action) as 'Sum'
createddate = min(table2_CreatedDate)
from table1 LEFT OUTER JOIN table2 ON table2_id = Table1_id
where table2_call = 'Y'
group by table1_id, table2_action

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 09:50:44

If you include table2_actionCreatedDate in select statement, you need to include it in Group by clause too. Otherwise use min or max of table2_actionCreatedDate in the select statement

Madhivanan

Failing to plan is Planning to fail

Leo_Don
Starting Member

42 Posts

Posted - 2011-01-27 : 10:13:56

Thank you,

but then i cannot choose the date… it will be min or the max :(

i just want to add Action created date in my select statement, so i can choose the date in my report generation

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 10:28:41

You have a single row for te group. That row has many dates from table2 - how are you going to have all of those dates in a single row?

Options - limit the number of dates and create a column for each (2 for min and max but you can have as many as you decide - but you have to decide at design time).
Duplicate the data for each date - but that will probably cause reporting issues
Include the detail data (i.e. one row per group and date and let the report do the aggregation after the choice of date.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources