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
168 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 65

Accepted Solution

by:
Jim Horn earned 400 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 73

Assisted Solution

by:sdstuber
sdstuber earned 100 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

13 Experts available now in Live!

Get 1:1 Help Now