Thread: Linking Queries

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Linking Queries


    Hi

    Just wondering if its possible to link queries together so they output to the same table?, what we are trying to achieve is say a player signs up in November, we want to know what their spend is in the first 28 days, then what their spend is 29 to 56 days, then what their spend is 57 to 84 days, every 28 days basically

    We are currently using this query for the first month

    SELECT COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END),
    SUM(TransactionDepositAmount)/100,
    SUM(TransactionWithdrawAmount)/100
    FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
    WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
    AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
    AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'
    AND TransactionRequestDateTime <= (FirstDepositDateTime + interval 28 day);

    this is to get us the number of depositors who have deposited in the first 28days, and also any withdrawals

    then we run this query to get us the next 28 days


    SELECT COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END),
    SUM(TransactionDepositAmount)/100,
    SUM(TransactionWithdrawAmount)/100
    FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
    WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
    AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
    AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'
    AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 29 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 56 day);

    So it outputs any player that deposited in Oct 11 their deposits between from day 29 to 56

    All works fine, and gets the data we want, just wondering if there is a way to link the queries together, so they output to one table, so then I have just one table with all the data, as currently im running the list of queries and its outputting each one separate, then im going through each one and typing my data into excel, and its tooks me about 5 hours today

    We just using oracle workbench fetching data from the server to work with

    Thanks
    Craig
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by wbauk2002
    just wondering if there is a way to link the queries together
    there is a way... UNION ALL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    instead of repeating the query again and again for each month, use grouping to get all data with a single query:

    sql Code:
    SELECT
    	(DATE_DIFF(TransactionRequestDateTime, FirstDepositDateTime) - 1) DIV 28 AS MON
    	, COUNT(DISTINCT CASE WHEN TransactionDepositAmount > 0 THEN PlayerAccounts.AccountID END)
    	, SUM(TransactionDepositAmount) / 100
    	, SUM(TransactionWithdrawAmount) / 100 
    FROM
    	-- fix me: use proper joins
    	PlayerAccounts, TransactionLogs, PlayerBookkeeping 
    WHERE	
    	PlayerAccounts.AccountID = TransactionLogs.AccountID 
    	AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID 
    	AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01' 
    GROUP BY
    	(DATE_DIFF(TransactionRequestDateTime, FirstDepositDateTime) - 1) DIV 28
    ;
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    Tried it. It says “DATE_DIFF does not exist”





    Originally Posted by Jacques1
    Hi,

    instead of repeating the query again and again for each month, use grouping to get all data with a single query:

    sql Code:
    SELECT
    	(DATE_DIFF(TransactionRequestDateTime, FirstDepositDateTime) - 1) DIV 28 AS MON
    	, COUNT(DISTINCT CASE WHEN TransactionDepositAmount > 0 THEN PlayerAccounts.AccountID END)
    	, SUM(TransactionDepositAmount) / 100
    	, SUM(TransactionWithdrawAmount) / 100 
    FROM
    	-- fix me: use proper joins
    	PlayerAccounts, TransactionLogs, PlayerBookkeeping 
    WHERE	
    	PlayerAccounts.AccountID = TransactionLogs.AccountID 
    	AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID 
    	AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01' 
    GROUP BY
    	(DATE_DIFF(TransactionRequestDateTime, FirstDepositDateTime) - 1) DIV 28
    ;
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by wbauk2002
    Tried it. It says “DATE_DIFF does not exist”
    try DATEDIFF instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo