Sql command for sum function

Question

0.00/5 (No votes)

See more:

Actually i have no idea about sql sum function. please help me how to write this command.

SQL

SELECT  B.cat_name as [cat_name],B.articles_count as [articles_count], B.cat_ID as [cat_ID],SUM(A.article_visit) AS [Article_Visit] FROM article_list A LEFT JOIN	category B ON A.cat_ID = B.cat_ID WHERE	A.article_status = 1 AND B.article_allow = 1 GROUP BY CUBE (B.cat_name,B.articles_count, B.cat_ID);

Thanks in advance

My previous command saw output like this
catId Name visit
1 Accounts 100
1 Accounts 50

But i want to do:

catId Name visit
1 Accounts 150

Error is:

SQL

error:
Column 'category.cat_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Posted 20-Jun-11 2:51am

rahul dev123

Updated 20-Jun-11 19:19pm

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**R. Giskard Reventlov** · Answer 1 · 2011-06-20T02:56:00

Solution 1

You need to add a group by[^] clause.

Posted 20-Jun-11 2:56am

R. Giskard Reventlov

Comments

rahul dev123 20-Jun-11 9:06am

i used this but shown error please help where i get wrong:
SELECT B.cat_name as [cat_name],B.articles_count as [articles_count], B.cat_ID as [cat_ID],SUM(A.article_visit) AS [Article_Visit] FROM article_list A LEFT JOIN category B ON A.cat_ID = B.cat_ID WHERE A.article_status = 1 AND B.article_allow = 1 GROUP BY CUBE (B.cat_name,B.articles_count, B.cat_ID);

R. Giskard Reventlov 20-Jun-11 9:42am

Am I supposed to read your mind to figure out what the error is? Why have you added CUBE?

rahul dev123 21-Jun-11 1:12am

if i remove CUBE then (,) are not accepted

R. Giskard Reventlov 21-Jun-11 3:14am

Try this:
select
b.cat_name,
b.articles_count,
b.cat_id,
sum(a.article_visit) article_visit
from
article_list a inner join
category b on a.cat_id = b.cat_id
where
a.article_status = 1
and b.article_allow = 1
group by
b.cat_name,
b.articles_count,
b.cat_id

thatraja 20-Jun-11 11:13am

Always include the error message in your question.

rahul dev123 21-Jun-11 1:14am

error:
Column 'category.cat_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Liju Sankar · Answer 2 · 2011-06-21T20:12:00

Try to modify the FROM clause in the below query

SQL

DECLARE @tmpTable as TABLE (ID INT, Description VARCHAR(10), Amount INT)
INSERT INTO @tmpTable (ID, Description,Amount) VALUES(1,'Amount', 100)
INSERT INTO @tmpTable (ID, Description,Amount) VALUES(1,'Amount', 50)

SELECT * FROM @tmpTable

--(1 row(s) affected)
--ID          Description Amount
------------- ----------- -----------
--1           Amount      100
--1           Amount      50

SELECT 
	ID,
	Description,
	SUM(Amount)AS Amount
FROM 
	@tmpTable
GROUP BY 
	ID,
	Description

--ID          Description 
------------- ----------- -----------
--1           Amount      150