sum

[Destramic]

hey guys ive tried to impliment a sum count in my query and i have the wrong syntax somewhere along the lines...can me anyone help please?

 

SELECT news_id, news, date, time, user_id, session_id, username, password, email, user_access FROM news INNER JOIN users USING (user_id),
SUM(news_id) AS comments FROM news_comments 
GROUP BY news_id
ORDER BY date

[mikosiko]

just to make you select valid...here

 

SUM(news_id) AS comments FROM news_comments

 

take out FROM news_comments

 

but I don't think you are doing what you apparently want... seems to me that you want to count the records in the table news_comments.. in such case you select is incorrect... you need to join the table news_comments too, replace SUM by COUNT and identify the field upon you are counting on properly (news_id is in both tables, news and news_comments right?)

[Destramic]

yes that is what i want  i need to cound the news comments do i need another join for this?

[mikosiko]

the answer is in my previous answer 

 

Ah.. and I forgot to modify your original select which also has the SUM in the wrong position

SELECT news_id, news, date, time, user_id, session_id, username, password, email, user_access FROM news INNER JOIN users USING (user_id),
SUM(news_id) AS comments FROM news_comments 
GROUP BY news_id
ORDER BY date

 

to be valid should be

SELECT news_id, news, date, time, user_id, session_id, username, password, email, user_access, SUM(news_id) AS comments FROM news INNER JOIN users USING (user_id)
GROUP BY news_id
ORDER BY date

 

add the other join and replace the SUM for a COUNT(news_comments.news_id)

[Destramic]

this is what ive been trying to do but im having no luck...what am i doing wrong please

SELECT news_id, news, date, time, user_id, session_id, username, password, email, user_access, COUNT(news_comments.news_id) AS comments
FROM news INNER JOIN users USING (user_id),
JOIN news_comments USING (user_id),
GROUP BY news_id
ORDER BY date

[mikosiko]

this is what ive been trying to do but im having no luck...what am i doing wrong please

SELECT news_id, news, date, time, user_id, session_id, username, password, email, user_access, COUNT(news_comments.news_id) AS comments
FROM news INNER JOIN users USING (user_id),
JOIN news_comments USING (user_id),
GROUP BY news_id
ORDER BY date

 

news should JOIN news_comments using news_id  most likely..... otherwise post your tables description

[Destramic]

im just having no luck at all i really dont see what the probelm is here is the tables

 

news

-----------------

news_id

news

date

time

user_id

 

news_comments

-------------------------

news_comments_id

news_id  (forigen key)

comments

 

can you please tell me why its no working?

 

SELECT `n.news_id`, `n.news`, `n.date`, `n.time`, `n.user_id`, 
	COUNT(nc.news_id) AS `comments`,
	FROM news AS n
LEFT JOIN news_comments AS nc ON nc.news_id = n.news_id
ORDER BY date

[Destramic]

i figured it out now

 

SELECT n.news_id, n.news, n.date, n.time, u.username,
	count(nc.news_id) AS comments
	FROM news n 
	LEFT OUTER JOIN news_comments nc ON n.news_id = nc.news_id
	INNER JOIN users u USING (user_id)
	Group by n.news_id

 

thanks for your help