How Do I Query For This Condition

Question

0.00/5 (No votes)

See more:

I have a problem to select these Data

SQL

table1

id amount businessVolume Dueno advisor_id
1   500     50            1     1
2   1000    100           2     1
3   500     50            15    1
4   1000    100           3     1
5   500     50            2     2
6   1000    100           3     2
7   500     50            13    2

this is my Table Format,
i have to sum the amount,and bv but there is a condition to sum ( amount) for whole dueno and to sum (business volume) where dueno <=12 group by advisor_id.

im expecting

SQL

amount businessvolume advisor_id
3000    250             1
2000    150             2

any suggestions ?
im going to implement this into my Recursive Query

Posted 4-Mar-14 1:03am

King Fisher

Updated 4-Mar-14 1:25am

v2

Add a Solution

3 solutions

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)

Peter Leow · Accepted Answer · 2014-03-04T01:30:00

Solution 2

SQL

SELECT SUM(t1.amount) amount,
(SELECT SUM(t2.businessvolume) FROM table1 t2 WHERE t2.dueno <=12
   AND t1.advisor_id=t2.advisor_id
   GROUP BY t2.advisor_id) businessvolumn,
advisor_id FROM table1 t1 GROUP BY t1.advisor_id

Posted 4-Mar-14 1:30am

Peter Leow

Comments

King Fisher 4-Mar-14 7:39am

thanks mr.peter ,i accept it .but is there any other way to do this,is it Possible?
just to know.no intention.

Peter Leow 4-Mar-14 8:10am

As much as possible, I always go for solution that is more generic, straight forward, and cross-platform. This solution will also work for MySQL. I am sure there are other ways of achieving this such as self join, but then why make thing so complicated.

King Fisher 4-Mar-14 8:16am

i think its much complicated to merge with recursive query ,so i just look for any new things by selfish.let me try with self join and thanks for your suggestion

Maciej Los 4-Mar-14 17:48pm

Looks OK, +5!
Please, see my answer ;)

King Fisher 4-Mar-14 23:43pm

my 5+,thank you so much

Maciej Los · Accepted Answer · 2014-03-04T11:45:00

Check this:

SQL

DECLARE @tmp TABLE (id INT IDENTITY(1,1), amount INT, businessVolume INT, Dueno INT, advisor_id INT)

INSERT INTO @tmp (amount, businessVolume, Dueno, advisor_id)
VALUES(500, 50, 1, 1), (1000, 100, 2, 1), 
(500, 50, 15, 1), (1000, 100, 3, 1), 
(500, 50, 2, 2), (1000, 100, 3, 2), 
(500, 50, 13, 2)

SELECT t1.advisor_id, (SELECT SUM(amount) FROM @tmp  WHERE advisor_id = t1.advisor_id) AS SumOfAmount, SUM(t1.businessVolume) AS SumOfBissVol
FROM @tmp AS t1
WHERE Dueno <13
GROUP BY advisor_id

Result:

adv._id Amount  Buss.Vol
1	3000	250
2	2000	150

Vedat Ozan Oner · Accepted Answer · 2014-03-04T01:14:00

Solution 1

[Updated]

select t1.advisor_id, t1.businessvolume, t2.amount from 
(select sum(businessvolume) as 'businessvolume', advisor_id
from table1
where Dueno<12
group by advisor_id) t1
inner join 
(select SUM(amount) as 'amount', advisor_id
from table1
group by advisor_id) t2 on t1.advisor_id=t2.advisor_id

I advice you to see here: GROUP BY[^]

and I used inner join here, but you may need to use outer join if t1 returns no rows. see here: Outer Join[^]

Posted 4-Mar-14 1:14am

Vedat Ozan Oner

Updated 4-Mar-14 1:49am

v3

Comments

King Fisher 4-Mar-14 7:23am

sorry sir,i am not expecting the Business volume for whole dueno.
sum(businessvolume) where dueno <=12 but sum(amount) where whole dueno

Vedat Ozan Oner 4-Mar-14 7:50am

updated.

King Fisher 4-Mar-14 23:48pm

my 4.bcoz, its usual one.this was i tried when before i post here and i was just looked for the new ideas.thank you so much for your timing.never mind :)

Maciej Los 4-Mar-14 17:48pm

Updated answer deserves for 5!

Vedat Ozan Oner 5-Mar-14 1:39am

thank you, all. trying to help.