Max of sum from two tables

Question

0.00/5 (No votes)

See more:

Hello!
Let's say I have two tables like this
tablea

people|trees
------|-----
jen | 2
mary | 4
john | 5
jen | 7
mary | 10

tableb

people|surname
--------------
mary |hue
jen |apple
john |orange

Now I want the max of the sum for the trees they have and I want to select the winner's surname and number of trees.

and I am like

SQL

SELECT  b.surname, a.people, sum(trees)
FROM tablea AS a INNER JOIN tableb AS b
WHERE a.people=b.people

And it always shows the correct amount of trees but with jen's name(the first entry).
What am I doing wrong?
Thank you!

Posted 10-Jun-14 11:20am

HopefullyCoder

Add a Solution

Comments

j snooze 10-Jun-14 17:24pm

Where is your group by clause? I don't see a max anywhere in your query?
You can at least get the sum of trees per person by adding
"Group By b.surname, a.people" to the end of your query.

HopefullyCoder 10-Jun-14 17:31pm

I know that. I want only one person with the total max of trees.
If I have max(trees) it shows the max amount of trees in an entry and still it's on jen's name!

2 solutions

Solution 1

Ah you want to sum the trees per person, then get who has the most. Sorry, late in the day for me. Here's just one way to do it. There are more, but this will get you the person with the most trees.
(I'm assuming thats what you want?) I made temp tables called #tree and #people instead of tablea and tableb.

--Solution 1
select a.people,b.surname,a.trees
From (Select top 1 people,SUM(trees) as trees
      from #tree
      Group by people
      Order by SUM(trees) desc) a
join #people b
on a.people = b.people

Posted 10-Jun-14 11:46am

j snooze

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2014-06-10T11:51:00

Solution 2

SQL

SELECT TOP 1
  b.surname,
  a.people,
  SUM(a.trees)
FROM
  tablea AS a
  INNER JOIN tableb AS b ON a.people = b.people
GROUP BY
  b.surname,
  a.people
ORDER BY
  SUM(a.trees) DESC

Adding some integer primary keys in there would bring it query performance. Joining two tables on a string field is a terrible habit.

Posted 10-Jun-14 11:51am

phil.o

Comments

HopefullyCoder 10-Jun-14 20:53pm

I have a problem with "SELECT TOP 1", it's in underlined as wrong

HopefullyCoder 10-Jun-14 21:03pm

fixed it with adding limit 1!thanks!