Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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