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
193 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 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 74

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

635 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