[Webinar] Streamline your web hosting managementRegister Today

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
?
205 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

640 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