Solved

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

Posted on 2010-11-09
25
403 Views
Last Modified: 2012-05-10
here is the layout in a basic form:

name   code   amount


I need to write a single select statement that will add all the amounts of each distinct code and also have it grouped by name.

For example, if one Name had 2 distinct codes but in 10 entries, I would want my results to be 2 rows, with the sum of each codes amounts.




 
0
Comment
Question by:RustyZ32
  • 10
  • 6
  • 4
  • +2
25 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
Hi,

Try this...

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


Cheers
0
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
select name, sum(amount) from table group by code,name
0
 
LVL 6

Author Comment

by:RustyZ32
Comment Utility
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.


0
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
can you post some sample data and required results
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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
0
 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility

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
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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.
0
 
LVL 6

Author Comment

by:RustyZ32
Comment Utility
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?
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
select name,code,sum(amount)
FROM yourTable
WHERE code IN (SELECT TableHoldingCodesToSum.CODE FROM TableHoldingCodesToSum)
GROUP BY code, name
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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.
0
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 500 total points
Comment Utility
CODES TO SUMMARIZE can be a table like @deighton said or parameters

(@param1, @param2 ...)

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)  -- Better like this.....


Cheers
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility
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
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
Hi @dmlyo150,

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


Cheers.

0
 
LVL 6

Author Comment

by:RustyZ32
Comment Utility
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!
0
 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility
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?
0
 
LVL 6

Author Comment

by:RustyZ32
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility
Glad I can help -and thank you -every point helps
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility

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.
0
 
LVL 8

Expert Comment

by:raulggonzalez
Comment Utility
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.

 
0
 
LVL 2

Expert Comment

by:dmlyo150
Comment Utility
"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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now