Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Posts
    38

    Unanswered: Counting entries in a table

    I'm completely lost. I can do this in code, but not SQL I think because I can't get my head around SQL

    I have a view called purchases(USER Integer, item TEXT, thedate DATE)

    What I want to see is how many times the user has bought b, c or d...(anything but 'a') after they purchased 'a'. If they purchased 'a' twice, then I only care about after the 2nd purchase and b, c or d don't count if they were bought at the same time or previously as 'a'. If they never purchased 'a' then I just want to get their total purchases. (I think this means thedate > max(thedate) where item = 'a' )
    So it may look like this:

    user | item | thedate
    ---------------------
    1 | b | 2012-05-05 -- don't care as next month he buys 'a'
    1 | a | 2012-06-05 -- So I only care about things after this date
    1 | b | 2012-06-05 -- same date, don't care
    1 | b | 2013-01-01 -- I need to count this one
    2 | c | 2013-01-01 -- I need to count this as user 2 never buys 'a'
    3 | a | 2012-05-05 -- don't start counting from here as user 3 buys another 'a' later
    3 | b | 2012-06-06
    3 | a | 2012-12-12 -- start counting after here
    3 | b | 2012-12-13 -- counts
    3 | d | 2012-12-13 -- counts
    4 | a | 2013-05-05 -- nothing purchased after 'a'


    The results I need would be
    user 1 = 1
    User 2 = 1
    user 3 = 2
    user 4 = 0

    TIA

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples.

    Note: If your DBMS doesn't support VALUES multi rows generator,
    replace it with other syntax(e.g. "SELECT ... [FROM ...] UNION ALL SELECT ... [FROM ...] UNION ALL ...").

    Example 1: Tested on DB2 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     purchases
    ( user_id , item , thedate ) AS (
    VALUES
      ( 1 , 'b' , DATE('2012-05-05') )
    , ( 1 , 'a' , '2012-06-05' )
    , ( 1 , 'b' , '2012-06-05' )
    , ( 1 , 'b' , '2013-01-01' )
    , ( 2 , 'c' , '2013-01-01' )
    , ( 3 , 'a' , '2012-05-05' )
    , ( 3 , 'b' , '2012-06-06' )
    , ( 3 , 'a' , '2012-12-12' )
    , ( 3 , 'b' , '2012-12-13' )
    , ( 3 , 'd' , '2012-12-13' )
    , ( 4 , 'a' , '2013-05-05' )
    )
    SELECT 'user ' || pm.user_id || ' = '
           || COUNT(pd.item) AS results
     FROM  (SELECT user_id
                 , MAX( CASE item
                        WHEN 'a' THEN thedate
                        ELSE          '0001-01-01'
                        END ) AS max_date
             FROM  purchases
             GROUP BY
                   user_id
           )         AS pm
     LEFT  OUTER JOIN
           purchases AS pd
     ON    pd.user_id = pm.user_id
       AND pd.thedate > pm.max_date
     GROUP BY
           pm.user_id
     ORDER BY
           pm.user_id
    ;
    ------------------------------------------------------------------------------
    
    RESULTS                       
    ------------------------------
    user 1 = 1                    
    user 2 = 1                    
    user 3 = 2                    
    user 4 = 0                    
    
      4 record(s) selected.
    Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT 'user ' || pm.user_id || ' = '
           || COUNT(pd.item) AS results
     FROM  (SELECT user_id
                 , MAX( CASE item
                        WHEN 'a' THEN thedate
                        ELSE          '0001-01-01'
                        END ) AS max_date
             FROM  purchases
             GROUP BY
                   user_id
           )         AS pm
     LEFT  OUTER JOIN
           purchases AS pd
     ON    pd.user_id = pm.user_id
       AND pd.thedate > pm.max_date
     GROUP BY
           pm.user_id
     ORDER BY
           pm.user_id
    ;
    Result:

    The following feature outside Core SQL-2003 is used:

    F591, "Derived tables"

    Example 2: Tested on DB2 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     purchases
    ( user_id , item , thedate ) AS (
    VALUES
      ( 1 , 'b' , DATE('2012-05-05') )
    , ( 1 , 'a' , '2012-06-05' )
    , ( 1 , 'b' , '2012-06-05' )
    , ( 1 , 'b' , '2013-01-01' )
    , ( 2 , 'c' , '2013-01-01' )
    , ( 3 , 'a' , '2012-05-05' )
    , ( 3 , 'b' , '2012-06-06' )
    , ( 3 , 'a' , '2012-12-12' )
    , ( 3 , 'b' , '2012-12-13' )
    , ( 3 , 'd' , '2012-12-13' )
    , ( 4 , 'a' , '2013-05-05' )
    )
    SELECT 'user ' || user_id || ' = '
           || COUNT( CASE
                     WHEN thedate > max_date THEN
                          item
                     END
                   ) AS results
     FROM  (SELECT p.*
                 , MAX( CASE item
                        WHEN 'a' THEN thedate
                        ELSE          '0001-01-01'
                        END
                      ) OVER(PARTITION BY user_id) AS max_date
             FROM  purchases AS p
           )
     GROUP BY
           user_id
     ORDER BY
           user_id
    ;
    ------------------------------------------------------------------------------
    
    RESULTS                       
    ------------------------------
    user 1 = 1                    
    user 2 = 1                    
    user 3 = 2                    
    user 4 = 0                    
    
      4 record(s) selected.
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT 'user ' || user_id || ' = '
           || COUNT( CASE
                     WHEN thedate > max_date THEN
                          item
                     END
                   ) AS results
     FROM  (SELECT p.*
                 , MAX( CASE item
                        WHEN 'a' THEN thedate
                        ELSE          '0001-01-01'
                        END
                      ) OVER(PARTITION BY user_id) AS max_date
             FROM  purchases AS p
           )
     GROUP BY
           user_id
     ORDER BY
           user_id
    ;
    Result:

    The following feature outside Core SQL-2003 is used:

    T611, "Elementary OLAP operations"
    F591, "Derived tables"
    Last edited by tonkuma; 05-14-13 at 21:25. Reason: Adjust blanks in Example 1.

  3. #3
    Join Date
    Jun 2005
    Posts
    38
    Thank you so much, I will open my book, and figure out how you got it to work :-)

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    I guess you already have a customers table.

    For test purpose:

    create table customers ("user" integer primary key);
    insert into customers values (1);
    insert into customers values (2);
    insert into customers values (3);
    insert into customers values (4);

    Code:
    select "user", (select count(*) from purchases p
                    where p."user" = c."user"
                      and not exists (select * from purchases
                                      where item = 'a'
                                        and "user" = p."user"
                                        and thedate >= p.thedate))
    from customers c;
    Core SQL-99 compliant.

    If you don't have a customers table, use the purchases table instead, combined with DISTINCT.

    Code:
    select distinct "user", (select count(*) from purchases p
                             where p."user" = c."user"
                               and not exists (select * from purchases
                                               where item = 'a'
                                                 and "user" = p."user"
                                                 and thedate >= p.thedate))
    from purchases c;

    PS. USER is a reserved word in ANSI SQL, thats why I had to write "user".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3:

    Mimer SQL Developers - Mimer SQL-2003 Validator

    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT p1.user_id
         , COUNT(*) - COUNT(p2.user_id) AS results
     FROM  purchases p1
     LEFT  OUTER JOIN
           purchases p2
      ON   p2.user_id =  p1.user_id
       AND p2.item    =  'a'
       AND p2.thedate >= p1.thedate
     GROUP BY
           p1.user_id
     ORDER BY
           user_id
    ;
    Result:

    Conforms to Core SQL-2003

  6. #6
    Join Date
    Jun 2005
    Posts
    38
    I'm impressed with myself, as I finally figured out what's happening in Example 1, that's not to say I'd be able to write it on my own ... I can understand what's happening and the logic behind it.

    I'm now down to my last call to the database and I can get back to the C++ GUI but I can't figure out the call.

    So I have the same table from above but now I need to know how many purchases have been made since the client was last phoned. There's a tabled called phone_calls and it has the phone_user_id int, last_called date, and it's own Integer Primary Key.

    I tried a gizzillion different lines of SQL and none of them seem to work, the closest I've got is

    SELECT user_id, COUNT(*) AS b1
    FROM purchases
    JOIN phone_calls ON phone_user_id = user_id
    WHERE
    (SELECT MAX(last_called) FROM phone_calls WHERE phone_user_id = user_id) < thedate
    GROUP BY user_id
    ORDER BY user_id


    That 'seems' to return the user_id purchase count since the last phone call BUT it doesn't list all the user_id. (4k user_id returned but 4.6K user_id It looks like the ones that have never been called aren't showing, but I need them too meaning count since their first purchase.

    TIA
    (I promise I'll spend the nights of my vacation reading SQL for dummies)
    Last edited by Jym; 05-17-13 at 00:22.

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    When you have a difficult problem, try to solve it in steps. E.g. skip the GROUP BY for a while.

    What does:
    Code:
    SELECT *
    FROM purchases
    JOIN phone_calls ON phone_user_id = user_id
    WHERE (SELECT MAX(last_called) FROM phone_calls
           WHERE phone_user_id = user_id) < thedate
    ORDER BY user_id
    return?

    And what happens if you change to an outer join?
    Code:
    SELECT *
    FROM purchases
    LEFT JOIN phone_calls ON phone_user_id = user_id
    WHERE (SELECT MAX(last_called) FROM phone_calls
           WHERE phone_user_id = user_id) < thedate
    ORDER BY user_id
    Does the result make any sense? If it does, it's time to put back that GROUP BY!

  8. #8
    Join Date
    Jun 2005
    Posts
    38
    Now I get no records ... uggh this is confusing

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about these?

    Example 4:
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT ps.user_id
         , COUNT(CASE
                 WHEN ps.thedate > ph.max_last_called THEN
                      0
                 END
                )
     FROM  purchases AS ps
     LEFT  OUTER JOIN
           LATERAL
           (SELECT MAX(last_called) AS max_last_called
             FROM  phone_calls AS ph
             WHERE ph.phone_user_id = ps.user_id
           ) AS ph
      ON   0=0
     GROUP BY
           ps.user_id
     ORDER BY
           user_id
    ;
    Result:

    The following feature outside Core SQL-2003 is used:

    T491, "LATERAL derived table"

    Example 5:
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT ps.user_id
         , COUNT(CASE
                 WHEN ps.thedate > ph.max_last_called THEN
                      0
                 END
                )
     FROM  purchases AS ps
     LEFT  OUTER JOIN
           (SELECT phone_user_id
                 , MAX(last_called) AS max_last_called
             FROM  phone_calls
             GROUP BY
                   phone_user_id
           ) AS ph
      ON   ph.phone_user_id = ps.user_id
     GROUP BY
           ps.user_id
     ORDER BY
           user_id
    ;
    Result:

    The following feature outside Core SQL-2003 is used:

    F591, "Derived tables"

  10. #10
    Join Date
    Jun 2005
    Posts
    38
    Tonkuma, thank you very much. I used Example 5 because I wasn't sure what a Lateral was and I think I got it right. I noticed that people that had never been phoned weren't showing in the list so I added a second WHEN

    WHEN ps.user_id NOT IN (SELECT phone_user_id FROM phone_calls) THEN '1901-01-01'

    The results look correct, can you tell me if I did it correctly?

    Thanks again. p.s. that one line took me a week of trial and error :-)

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If a user never been phoned, ph.max_last_called would be NULL.
    So, to get count for the user, try
    Code:
         , COUNT(CASE
                 WHEN ps.thedate > ph.max_last_called
                  OR  ph.max_last_called IS NULL      THEN
                      0
                 END
                )
    or
    Code:
         , COUNT(CASE
                 WHEN ps.thedate
                      > COALESCE(ph.max_last_called , '1901-01-01') THEN
                      0
                 END
                )

  12. #12
    Join Date
    Jun 2005
    Posts
    38
    Thank you, looks so simple when you do it :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •