using COUNT in multiple queries

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 2000 Forums

SQL Server Development (2000)

using COUNT in multiple queries

Author

Topic

cire
Starting Member

15 Posts

Posted - 2006-03-05 : 21:45:36

Hi all, i have a query which queries multiple tables in a database, linked by joins. I inserted a COUNT(table_name) at the end of my select statement but i got this error:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'act_log.last_mod_dt' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'act_log.action_desc' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'act_log.type' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'act_log.description' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.ref_num' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'ctct.c_last_name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'prob_ctg.sym' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.close_date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.status' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.description' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.summary' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.priority' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.urgency' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.open_date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'int_org.iorg_name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'loc.l_name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.customer' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.open_date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'call_req.open_date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

putting it in front doesnt solve it, after which i put a GROUP BY statment with all the tables shown above and i get this error:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

so how do i actually count the no of rows of the column that i want- after the query is executed.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 22:04:43

try to add the count(table_name) as a sub query

select col1, col2 col3, (select count(*) from sometable where somecondition)
from   table1 inner join ....
where  ..

----------------------------------
'KH'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-06 : 01:10:28

COUNT is an aggregate function, and any query that mixes aggregate and non-aggregate results must have a GROUP BY clause. Post your code, and we can show you how to clean it up.

cire
Starting Member

15 Posts

Posted - 2006-03-06 : 01:23:11

hmm the errors are gone but the count returns the total no of rows for the selected column and not the the amount of rows displayed in the result. i.e. i stated where clauses for the entire query - placed at the end
WHERE (call_req.status = 'CL') AND (ctct.c_last_name LIKE 'gallardo')

running the query returns 1 record but the count returns the total amount of rows disregarding the where clause. Even if i put the above WHERE statement inside the sub-query, it still returns the total amount of rows.

Whats the problem?

Thanks

cire
Starting Member

15 Posts

Posted - 2006-03-06 : 04:48:52

SELECT svd.call_req.ref_num AS ticket_no, DATEADD(ss, svd.act_log.last_mod_dt, '1970-1-1') AS last_modified_date,
svd.act_log.action_desc, svd.act_log.type AS activity_type, svd.act_log.description AS comment, svd.ctct.c_last_name AS Modified_by_analyst_last_name, svd.prob_ctg.sym AS request_area, DATEADD(ss, svd.call_req.close_date, '1970-1-1') AS close_date, svd.call_req.status AS ticket_status, svd.call_req.description AS ticket_description, svd.call_req.summary AS ticket_summary,
svd.call_req.priority, ctct_2.c_last_name AS assignee_last_name, DATEADD(ss, svd.call_req.open_date, '1970-1-1')
AS open_date, svd.int_org.iorg_name AS int_org, svd.loc.l_name AS location, ctct_1.c_last_name AS affected_end_user,
DATEPART(week, DATEADD(ss, svd.call_req.open_date, '1970-1-1')) AS Week, DATEPART(month, DATEADD(ss, svd.call_req.open_date, '1970-1-1')) AS mth, DATEPART(year, DATEADD(ss, svd.call_req.open_date, '1970-1-1')) AS year

FROM svd.act_log INNER JOIN
svd.call_req ON svd.call_req.persid = svd.act_log.call_req_id INNER JOIN
svd.ctct ON svd.act_log.analyst = svd.ctct.id
INNER JOIN
(SELECT MAX(last_mod_dt) AS date_modified, call_req_id
FROM svd.act_log GROUP BY call_req_id) mod_dates ON svd.act_log.call_req_id = mod_dates.call_req_id AND
svd.act_log.last_mod_dt = mod_dates.date_modified
INNER JOIN
svd.prob_ctg ON svd.call_req.category = svd.prob_ctg.persid INNER JOIN
svd.int_org ON svd.ctct.c_org_id = svd.int_org.id
INNER JOIN
svd.loc ON svd.ctct.c_l_id = svd.loc.id
INNER JOIN
svd.ctct ctct_1 ON svd.call_req.customer = ctct_1.id
INNER JOIN
svd.ctct ctct_2 ON svd.call_req.assignee = ctct_2.id
WHERE (svd.call_req.status <> 'CL')
ORDER BY DATEPART(year, DATEADD(ss, svd.call_req.open_date, '1970-1-1')) DESC, svd.call_req.ref_num DESC, DATEADD(ss,
svd.act_log.last_mod_dt, '1970-1-1') DESC

so how do i count the no of rows in svd.ref_num AFTER it has executed the query, i.e. the total no of rows with status <> 'CL'

Thanks
Eric

Thanks

cire
Starting Member

15 Posts

Posted - 2006-03-06 : 21:06:42

anyone?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources