December 6, 2004 at 6:04 am
Hi every one...
I execute this query :
SELECT news_content.id_doc AS ID, news.publish_date, news.publish_time, news_content.title, news_content.abstract, news_content.lead, news.image_title,
news.image_main,
SUM(doc_view_stats.hour_00 + doc_view_stats.hour_01 + doc_view_stats.hour_02 + doc_view_stats.hour_03 + doc_view_stats.hour_04 + doc_view_stats.hour_05
+ doc_view_stats.hour_06 + doc_view_stats.hour_07 + doc_view_stats.hour_08 + doc_view_stats.hour_09 + doc_view_stats.hour_10 + doc_view_stats.hour_11
+ doc_view_stats.hour_12 + doc_view_stats.hour_13 + doc_view_stats.hour_14 + doc_view_stats.hour_15 + doc_view_stats.hour_16 + doc_view_stats.hour_17
+ doc_view_stats.hour_18 + doc_view_stats.hour_19 + doc_view_stats.hour_20 + doc_view_stats.hour_21 + doc_view_stats.hour_22 + doc_view_stats.hour_23)
AS daily_stats
FROM doc_view_stats INNER JOIN
news_content ON doc_view_stats.doc_id = news_content.id_doc INNER JOIN
news ON news_content.id_doc = news.ID
WHERE (news.[section] = '1') AND (news_content.lang = 'fa') AND (news_content.published = '1') AND (doc_view_stats.doc_type = '1') AND
(doc_view_stats.[date] >= '') AND (doc_view_stats.[date] <= '1383-09-16')
GROUP BY news_content.id_doc, news.publish_date, news.publish_time, news_content.title, news_content.abstract, news_content.lead, news.image_title,
news.image_main
ORDER BY daily_stats DESC
but I had error that say :
"The text,ntext and image data types can not be compared or sorted, except when using IS NULL or LIKE operator..."
I know that it's because news_content abstract and news_content.lead in GROUP BY...because these fields are Text....(column type)
but if remove them from GROUP BY I have famous error :
"Column <column name> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
please help me...
I don't know what i can do....
December 6, 2004 at 8:40 am
Try something like this:
select t1.f1, t1.f2, t1.f3, t2.sum1, t2.sum2, t2.sum3 from t1 inner join (select sum(field1) sum1, sum(field2) sum2, sum(field3) sum3, joinFIeld from aggTable group by joinField) t2 on t1.joinField = t2.joinField
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply