November 29, 2021 at 12:08 pm
2 tables
Users : User_id, First_name, Last_name, Gender.
Deposits : User_id, time_stamp, amount.
i need to write a query that returns as result all users with Gender="M" from User table and for each user his total sum amount from deposits table taking into account only deposits made after 1/1/17 and on amount which is higher than 10. If user doesn’t have any such deposits make sure that user is still part of result with sum deposits 0.
thanks
November 29, 2021 at 3:03 pm
What's PGADMIN?
Is this homework?
What have you tried? Can you show us what you have attempted?
Can you take a look at this article, which describes how to post a question so we can help you find better answers faster.
http://www.sqlservercentral.com/articles/61537
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 29, 2021 at 3:07 pm
I agree, sounds very much like class work.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2021 at 3:22 pm
pgAdmin appears to be some sort of PostGreSQL tool
Perhaps yo would get better answers from a PostGreSQL forum.
November 29, 2021 at 3:43 pm
Yes- its homework (important one)
pgadmin is the software i use,
this is what i try so far-
SELECT users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp, SUM(deposit.amount) AS TotalAmount FROM users
INNER JOIN deposit
ON users.user_id = deposit.user_id
GROUP BY users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp
HAVING users.gender = 'M' AND deposit.time_stamp > '01/01/2017' AND SUM(deposit.amount) > 10
It brings me the result, but I have a hard time implementing the last sentence
"If user doesn’t have any such deposits make sure that user is still part of result with sum deposits 0"
November 29, 2021 at 3:50 pm
Ah, it's asking you to do a LEFT JOIN and then deal with the NULL values to arrive at the value of zero (0) for anyone who doesn't have transactions. For that, you want to look at COALESCE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2021 at 3:58 pm
Ah, it's asking you to do a LEFT JOIN and then deal with the NULL values to arrive at the value of zero (0) for anyone who doesn't have transactions. For that, you want to look at COALESCE.
Does COALESCE exist in PostgreSQL?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 29, 2021 at 4:02 pm
ISNULL doesn't exist, but COALESCE is there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2021 at 4:08 pm
Yes- its homework (important one)
pgadmin is the software i use,
this is what i try so far-
SELECT users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp, SUM(deposit.amount) AS TotalAmount FROM users
INNER JOIN deposit
ON users.user_id = deposit.user_id
GROUP BY users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp
HAVING users.gender = 'M' AND deposit.time_stamp > '01/01/2017' AND SUM(deposit.amount) > 10
It brings me the result, but I have a hard time implementing the last sentence
"If user doesn’t have any such deposits make sure that user is still part of result with sum deposits 0"
I think this may not correct:
SUM(deposit.amount) > 10
Based upon your explanation, I think the assignment is asking to include any individual deposit greater than 10.
So, if a person has deposits of 10, 2, 5, and 20, only the 10 and 20 would be included.
But, IMHO, that calculation would make little sense to return. Having the exact requirements from the assignment would be helpful.
Also, PGADMIN is an open source tool to manage PostgreSQL SSMS (SQL Server Management Studio) is what Microsoft created for SQL Server. While you are technically correct in saying "pgadmin is the software i use", what you really need to say that "pgadmin is the interface I use to query the data in a PostgreSQL database". The RDMS you are using does matter.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 29, 2021 at 4:56 pm
As noted, a LEFT JOIN is what you need. An INNER JOIN requires a matching row in both tables; a LEFT JOIN will keep the row from the left-hand table in the join even if no matching row is found in the right-hand table.
If there is no matching row, SQL will set all columns in the right-hand table to NULL. You can take advantage of this to check for missing rows. You shouldn't need to use COALESCE, as SQL will ignore NULL values when doing a SUM.
I coded your date requirement exactly as you stated it (after Jan 1, i.e., Jan 2), although it seems very odd. It's more likely it should be >= '01/01/2017' rather than >.
SELECT users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp, SUM(deposit.amount) AS TotalAmount
FROM users
LEFT OUTER JOIN deposit ON users.user_id = deposit.user_id
WHERE users.gender = 'M' AND deposit.time_stamp >= '01/02/2017'
GROUP BY users.users_id, users.first_name, users.last_name, users.gender, deposit.time_stamp
HAVING SUM(deposit.amount) > 10 OR SUM(deposit.amount) IS NULL /* --<<-- */
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply