?
Solved

SQL - sum of rows in a new row?

Posted on 2008-10-01
2
Medium Priority
?
479 Views
Last Modified: 2012-08-13
Hi.. I have a table with 6 rows/records of data.
The last three rows I would like summerized into a separate record with a make-table query, is this possible?

Basically, everytime I run the report I need to combine
Company B, Account 2
Company B, Account 1
Company B, Account 0

into a separate row.

Company	Account	Max Price
D	4	126
A	4	1205
A	0	519
C	4	308
C	0	55
B	4	440
B	3	20
B	2	171
B	1	95
B	0	2318

Open in new window

0
Comment
Question by:NO_CARRIER
2 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 300 total points
ID: 22616948
Are you looking for something like this?

SELECT Company, IIf([Company]="B" And [Account] In ("0","1","2"),"B2",[Account]) AS Acct, Sum(Max_Price) AS SumOfMax_Price
FROM MyTable
GROUP BY Company, IIf([Company]="B" And [Account] In ("0","1","2"),"B2",[Account]);

OM Gang
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 22623639
Awesome, exactly what I was looking for... (and they said it couldn't be done, ha!)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

589 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