December 6, 2004 at 7:02 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 7:15 am
SELECT nc.id_doc AS [ID],
n.publish_date,
n.publish_time,
n_content.title,
n_content.abstract,
n_content.lead,
n.image_title,
n.image_main,
st.daily_stats
FROM (
SELECT doc_id,
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
WHERE (doc_view_stats.doc_type = '1')
AND (doc_view_stats.[date] >= '')
AND (doc_view_stats.[date] <= '1383-09-16')) st
INNER JOIN news_content nc
ON nc.id_doc = st.doc_id
INNER JOIN news n
ON n.[ID] = nc.id_doc
AND (n.[section] = '1')
AND (n_content.lang = 'fa')
AND (n_content.published = '1')
ORDER BY st.daily_stats DESC
Far away is close at hand in the images of elsewhere.
Anon.
December 6, 2004 at 7:19 am
Very nice, David. The force is strong with you!
Grouping on many non-primary key columns, usually indicates time for subquery (derived table).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply