help with SQL query PGADMIN

  • 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

  • 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/

  • I agree, sounds very much like class work.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • pgAdmin appears to be some sort of PostGreSQL tool

    Perhaps yo would get better answers from a PostGreSQL forum.

  • 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"

     

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    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/

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • crisy wrote:

    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/

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply