Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I use GROUP BY on one item without having every item in SELECT as an aggregate function or GROUP BY

Posted on 2014-11-19
2
Medium Priority
?
202 Views
Last Modified: 2014-11-19
I have a SQL with several (21) items in the SELECT part JOINing 6 tables.
I want to sum one of the fields; SUM(cost) and GROUP BY cost.
Can I do this without having to use all the rest of the fields in the GROUP BY ?

Example:
SELECT a.field1, SUM(a,field2), b.field1, c.field4,...(there's also a subquery in here)
FROM table1 AS a
LEFT JOIN table2 AS b
...
WHERE ...
GROUP BY a.field2
ORDER BY...


- or do I ave to use another subquery?
0
Comment
Question by:fmsol
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1600 total points
ID: 40452983
Yes.  Using your code, and inserting id as the PK for table1
SELECT a.field1, a_sum.field_2_sum, b.field1, c.field4,...(there's also a subquery in here)
FROM table1 AS a
  JOIN (
     SELECT id, SUM(Field2) as field_2_sum
     FROM Table1 
     GROUP BY id) a_sum as a.id = a_sum.id
LEFT JOIN table2 AS b
...
WHERE ...
GROUP BY a.field2
ORDER BY...

Open in new window

For more reading here's an article called SQL Server GROUP BY Solutions that has a wompload of examples, and point #5 addresses your question.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 40452988
use the analytic SUM instead of the aggregate SUM,  

just move your aggregate group by column list to the analytic partition by list

however, it usually doesn't make sense to SUM by the same value you're using the create GROUPS.

If you group by COST, then each cost will be one row, so the SUM of that row will be itself.
So, you group by(or partition by) some other field(s).  It is possible to group/partition by the same value, it's just unusual.



SELECT a.field1, SUM(a.cost) over(partition by a.field2) , b.field1, c.field4,...(there's also a subquery in here)
FROM table1 AS a
LEFT JOIN table2 AS b
...
WHERE ...
ORDER BY...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

824 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