13 Replies - 365 Views - Last Post: 18 June 2019 - 11:36 AM Rate Topic: -----

#1 livium   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 551
  • Joined: 21-December 08

Just one sql querry

Posted 15 June 2019 - 11:43 PM

Hello!

Suppose one has the following info:

name        | money
   -------------+--------
    Andrea      | -10000
    Mark        | 149513
    Kassidy     |  -5016
    Andrea      |  17500
    Andrea      |   2500
    Jim         | 100000
    Jim         |    -50
    Jim         |    -50
    Jim         |    -50
    Kassidy     |  -2013



I am supposed to make a querry that will modify the above info into the following:


name       | sum_of_deposits  | sum_of_withdrawals
    ------------+------------------+--------------------
     Andrea     |  20000           | 10000
     Jim        | 100000           |   150
     Kassidy    |      0           |  7029
     Mark       | 149513           |     0


How do you do this?

This post has been edited by livium: 15 June 2019 - 11:48 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Just one sql querry

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Just one sql querry

Posted 16 June 2019 - 01:44 AM

SQL sub queries... joing on a common id.. and using the aggregate 'sum'.
Was This Post Helpful? 1
  • +
  • -

#3 livium   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 551
  • Joined: 21-December 08

Re: Just one sql querry

Posted 17 June 2019 - 10:17 AM

Hmmm... I dont think it is that easy.
Was This Post Helpful? 0
  • +
  • -

#4 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

Re: Just one sql querry

Posted 17 June 2019 - 10:23 AM

what do you mean it isnt that easy?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Just one sql querry

Posted 17 June 2019 - 03:15 PM

View Postlivium, on 17 June 2019 - 12:17 PM, said:

Hmmm... I dont think it is that easy.

Funny thing is I did it that way on my own SQL query. *shrug* So it goes.

name                 sum_of_deposits sum_of_withdrawals
-------------------- --------------- ------------------
Andrea               20000           10000
Jim                  100000          150
Kassidy              0               7029
Mark                 149513          0

Was This Post Helpful? 1
  • +
  • -

#6 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Just one sql querry

Posted 18 June 2019 - 05:24 AM

It really is that easy, but unlike modi, I don't have enough information to do a sum on records when all that was given was total amounts.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Just one sql querry

Posted 18 June 2019 - 06:45 AM

@astone - I operated under the assumption of 'less than 0' and 'greater than or equal to 0' for withdrawl/deposit.
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Just one sql querry

Posted 18 June 2019 - 07:01 AM

LOL
Was This Post Helpful? 0
  • +
  • -

#9 livium   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 551
  • Joined: 21-December 08

Re: Just one sql querry

Posted 18 June 2019 - 09:15 AM

View Postmodi123_1, on 17 June 2019 - 04:15 PM, said:

View Postlivium, on 17 June 2019 - 12:17 PM, said:

Hmmm... I dont think it is that easy.

Funny thing is I did it that way on my own SQL query. *shrug* So it goes.

name                 sum_of_deposits sum_of_withdrawals
-------------------- --------------- ------------------
Andrea               20000           10000
Jim                  100000          150
Kassidy              0               7029
Mark                 149513          0


So what is the query?
Was This Post Helpful? 0
  • +
  • -

#10 xclite   User is offline

  • I wrote you an code
  • member icon


Reputation: 1393
  • View blog
  • Posts: 4,225
  • Joined: 12-May 09

Re: Just one sql querry

Posted 18 June 2019 - 09:18 AM

What have you tried? We don't just provide code here.
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Just one sql querry

Posted 18 June 2019 - 09:37 AM

View Postlivium, on 18 June 2019 - 11:15 AM, said:

[...snip....]
So what is the query?


Oh you guys.. almost got me there!

Posted Image

It's as I explained above, and using specific WHERE clauses in each subquery. Let's see what you tried first.
Was This Post Helpful? 0
  • +
  • -

#12 livium   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 551
  • Joined: 21-December 08

Re: Just one sql querry

Posted 18 June 2019 - 09:54 AM

View Postmodi123_1, on 18 June 2019 - 10:37 AM, said:

View Postlivium, on 18 June 2019 - 11:15 AM, said:

[...snip....]
So what is the query?


Oh you guys.. almost got me there!

Posted Image

It's as I explained above, and using specific WHERE clauses in each subquery. Let's see what you tried first.


I have created 2 views. And then made an union. Then I think I should have used a cursor. I dont have the code anymore. It was a codility test and I failed.
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Just one sql querry

Posted 18 June 2019 - 09:56 AM

No.. not views. Subqueries. Union is not a join so yeah, not that either.

A cursor could do it, but definitely about six miles out beating around the bush.

https://docs.microso...sql-server-2017
Was This Post Helpful? 0
  • +
  • -

#14 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

Re: Just one sql querry

Posted 18 June 2019 - 11:36 AM

subquery:

select something from somewhere where something (= < > like ) select something where etc
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1