SQL help with query

Databases
1

I have a SQLite database with a table called transactions with the following fields: UserID, TransDate,TransCode.
I need to be able count the number of Users who have a TransCode=5 and TransCode=6 and count the number of days between the 2 transactions.

I couldn’t work out how to check for two records with the same ID and matching my TransCode criteria, let alone count up the occurrences.

Any help appreciated.

Cheers

Paul

2

Define # of days… since you are asking for multiple metrics here

of users is easy… but that is a single value

of days would differ for each user

what if a given user has more that one 5 or 6 transaction
what if they have a 5 but not a 6 or vice versa

3

Let’s assume that everyone will have one code 5 transaction and one code 6 transaction either on the same day or any number of days later. I was going to calculate the average days between the two transactions but how do you check for a user having a code 5 and a code 6 record?

4

You’ll want to join the table to itself so you can check both the code 5 and code 6 criteria. Perhaps something like this:

SELECT t2.TransDate - t1.TransDate
FROM transactions t1, transactions t2
WHERE t1.TransCode = 5
AND t2.TransCode = 6
And t1.UserID = t2.UserID

5

[quote=52261:@Paul Lefebvre]You’ll want to join the table to itself so you can check both the code and code 6 criteria. Perhaps something like this:

SELECT t2.TransDate - t1.TransDate
FROM transactions t1, transactions t2
WHERE t1.TransCode = 5
AND t2.TransCode = 6
And t1.UserID = t2.UserID[/quote]

to expand upon that

SELECT count(*) as num_users, (t2.TransDate - t1.TransDate)/count(*)
FROM transactions t1, transactions t2
WHERE t1.TransCode = 5
AND t2.TransCode = 6
And t1.UserID = t2.UserID

UNTESTED… but that should give you the number of users with a 5 and 6, plus AVG days between transactions

6

Thanks Dave and Paul, I didn’t realise you could join a table to itself.
I’ll give your suggestions a go and see what I get!

Cheers

Paul

7

Just been playing around with this and I now feel confident in being able to extract the info I need.
Thanks again for your input.

8

correction

SELECT count(*) as num_users, SUM(t2.TransDate - t1.TransDate)/count(*)
FROM transactions t1, transactions t2
WHERE t1.TransCode = 5
AND t2.TransCode = 6
And t1.UserID = t2.UserID