Multiple sum's of the same column based on another column in the one query

Hi,

Try this...

SELECT code, name, sum(amount)
FROM yourTable
GROUP BY code, name


Cheers

select name, sum(amount) from table group by code,name

I'm sorry. I  was not clear in my question.

The main challenge is that several of the values in the code column need to stay unsummarized.

i need a way to manually specify which values are to be summed and which are to be left alone.

can you post some sample data and required results

Is it going to be more than one at a time?

If not, you can use a parameter

SELECT code, name,
sum(
CASE WHEN @parameter = 1 then amount
when @parameter = 2 then anotherField
END
)
FROM yourTable
GROUP BY code, name


If not, give more info...


Cheers

Is there any limitations to your code that you could not use an ORDER clause? See the following:

SELECT Name, Code, sum(Amount) FROM table GROUP BY Name, Code
ORDER BY Name, Code

Hi,

There's no incompatibility between GROUP BY and ORDER BY, so you can use them both.

'Group by' sort the results somehow, but if you need any specific order, you can use ORDER BY.


Cheers.

yes, it will be more than one at a time. I might need 5 of the codes summed and 3 not summed.

can I do some kind of series of nested statements with individual WHERE clauses for the different codes?

select name,code,sum(amount)
FROM yourTable
WHERE code IN (SELECT TableHoldingCodesToSum.CODE FROM TableHoldingCodesToSum)
GROUP BY code, name

HI,

can you use dynamic SQL? you can build the query according a parameter (or some) ...

anyway, can you post the field and an example of what is your expected results?


cheers.

Sorry, I think I got it...

you say that some codes need to be summarize and some not?

SELECT name,code,sum(amount)
FROM yourTable
WHERE code IN (CODES TO SUMMARIZE)
GROUP BY code, name
UNION

SELECT name,code,amount
FROM yourTable
WHERE code IN (CODES NOT  TO SUMMARIZE)



Cheers.

add the Order By clause after the Union Select.
SELECT name,code,sum(amount)
FROM yourTable
WHERE code IN (CODES TO SUMMARIZE)
GROUP BY code, name
UNION

SELECT name,code,amount
FROM yourTable
WHERE code NOT IN (CODES TO SUMMARIZE)
ORDER BY name, code

Hi @dmlyo150,

you win... but @RustyZ32 didn't mention anything about ordering....


Cheers.

dmly0150

that worked!

however, I also have several other columns I would like to include in the select statement but when i put them in the GROUP BY clause it messes up the sums. (i get an errror if i dont put them in group by)

I know this isn't part of the original question and will accept your solution regardless.


thanks!

thank you -but I can stil help.
There is a PARTITION feature as of SQL Server 2005.
can you provide me (all) the field names and an idea of how you want it to apear with the addtional fields?

dmlyo150, I figured it out. In the first select statement i left out any columns that I knew had unique data. the ones that didn't (i.e. an employees office) i was able to include without messing up the grouping.

i then specified the rest in the second statement after the union.


all worked well, thanks for your help.

Glad I can help -and thank you -every point helps

Hi,

I have to add that the solution posted by @dmlyo150 is a copy/paste of my solution, so the accepted answer should be mine.

I want to open this question to be reviewed by a moderator.


Cheers

raulg - I'm with you on that - it looks like a copy of your post with little further amplification

I respect the fact that you mentioned that your explanation added to my previous answer BTW, that's the way it should be, it was hard to see what was needed here.

Hi @deighton

Thanks for your support. And apart, if you read the whole thread, he was asking me whether it was possible to use GROUP BY and ORDER BY together...

Cheers.

Administrative Moderator,
I accept your position, and thank you for imparting the Experts Exchange etiquette. I urge the moderator to review the initial post of this thread. I answered the Question! Correctly! Later the question was expanded requiring use of a UNION; this provided RustyZ32 with all the data (he) was looking for –which was NOT in the original question –but it still wasn’t summarized as he indicated it needed to be in his original question. Once again –I indicated using the Order by Clause –which he must have felt was pretty crucial because he awarded me the points.
 
---
raulggonzalez:
Hi @dmlyo150,

you win... but @RustyZ32 didn't mention anything about ordering....

Cheers.
---
RustyZ32 did say he wanted it order when he wrote… “a single select statement that will add all the amounts of each distinct code and also have it grouped by name”
If RustyZ32 did not put an Order By clause on this –it would have been grouped –first by code then by name. My Order By clause returned it grouped by name then code –as his original question asked.
_alias99 wrote that the best practice would have been to post - (for example, with a comment to the effect of "adding 'xyz' to the code posted by 'ExpertA' would be beneficial because ...").
My post read exactly that –my copy/paste was proceeded by -- add the Order By clause after the Union Select.
In all fairness I think RustyZ32 should at least have the opportunity to distribute the points to more than one contributor.

Hi @dmlyo150,

When I said 'you win' was an irony.

There's nothing in @RustyZ32 original or 2nd question about ordering, that's why I gave up, because you where saying the same like I did + ORDER BY  TWO TIMES, which as long as I read, wasn't necessary.

And your ORDER BY is indeed mine as well, because you were asking me whether you can use both, and I said yes.

I don't want to get deeper and the moderator explained what should be pretty well. I'm not in this website since long, but I got how it works.

Best regards.

 

"And your ORDER BY is indeed mine as well, because you were asking me whether you can use both, and I said yes."

I don't know why you would think I was asking -another contributor -questions about code. I was asking RustyZ32 if there was a limitation to this code that he could NOT use an ORDER BY clause.
for example –It is possible an author may not give you the entire scenario –for instance if he was going to be using this Single Select as a subquery in his final solution –he would be unable to use an Order By clause. I am not naive enough to think that this one and only Select statement is going to complete his entire project.

Sincere Wishes