CODEWITHSUNDEEP
mysqlsql
dabernathy89
dabernathy89

Reputation: 49

grouping records conditionally

My Google-fu is coming up short on this one. I've got a table of transactions, like this:

id      email                   source      amount  timestamp
1       [email protected]      vendor      10      2014-03-10 23:34:40
2       [email protected]        website     15      2014-03-11 13:30:00
3       [email protected]        website     50      2014-03-11 17:30:00
4       [email protected]      website     65      2014-03-13 20:06:30
5       [email protected]        vendor      10      2014-03-14 16:20:30

I want to be able to group these by email, but only for users who:
A) came in through the 'vendor' source initially, and
B) also made a transaction through the 'website' source.
So for the above sample data, I would want this:

email                       total_amount        transactions
[email protected]          75                  2

Mary would not be included because her first transaction was through 'website', and not 'vendor'. John would not be included because he did not have a transaction through the vendor at all.

EDIT:

Less ideal, but still useful, would be this result set:

email                       total_amount        transactions
[email protected]          75                  2
[email protected]            60                  2

Where Mary and Daniel are both included because they both came in through the 'vendor' source in at least one transaction.

Upvotes: 0

Views: 43

Answers (4)

JDGuide
JDGuide

Reputation: 6525

Try this :-

select x1.email_id,(x1.tot + x2.tot)as total_amount,(x1.cnt + x2.cnt)as transactions from 
(select t1.email_id,count(t1.email_id)as cnt,sum(t1.totalamt)as tot from testdata t1 where t1.sourcee='web' group by t1.email_id)x1
inner join (select t2.email_id,count(t2.email_id)as cnt,sum(t2.totalamt)as tot from testdata t2 where t2.sourcee='vendor' group by t2.email_id)x2
on x1.email_id=x2.email_id group by x1.email_id;

Output :-

enter image description here

Its working fine.If required you please change the field name as per your table structure. Hope it will help you.

Upvotes: 0

Terje D.
Terje D.

Reputation: 6315

This query should give you the desired result by using a subquery to find the persons that have an initial 'vendor' record followed by a 'website' record, before collecting the summary information from the records for these persons.

If you remove the lines marked with -- *, persons whose 'vendor' record is not their first one is also included.

SELECT email, SUM(amount) AS total_amount, COUNT(*) AS transactions
FROM transactions
WHERE email IN 
    (SELECT t1.email FROM transactions t1
    LEFT JOIN transactions t0                                -- * 
    ON t0.email = t1.email AND t0.timestamp < t1.timestamp   -- *
    LEFT JOIN transactions t2 
    ON t2.email = t1.email 
    WHERE  t1.source = 'vendor' AND t2.source = 'website'
    AND t0.email IS NULL                                     -- *
    )
GROUP BY email;

See http://www.sqlfiddle.com/#!2/864898/8/0

Upvotes: 1

xQbert
xQbert

Reputation: 35323

SELECT A.Email, sum(B.Amount) as Total_Amount, count(B.time) as Transactions
FROM tableName A
INNER join tableName B 
 on A.Email=B.Email 
 AND A.source='vendor' 
Group By A.Email

Requirements are a bit unclear as you initially indicate the must initially come though vendor, but then you retract that statement later by adding mary.

http://sqlfiddle.com/#!2/bb4f9/1/0

If date/timestamps are important add an AND clause for A.Time<= B.Time and aggregrate the A.Amoun t and A.time and add those in like...

SELECT A.Email, sum(B.Amount)+ sum(A.Amount) as Total_Amount, count(B.time)+count(A.Time) as Transactions
FROM tableName A
INNER join tableName B 
 on A.Email=B.Email 
 AND A.source='vendor'
 and A.Time<=B.Time
Group By A.Email

But this assumes vendor entry will only occur once for each email

So this solution first finds a vendor entry (if there's more than one for an email address this will not return accurate counts) then it finds any entries for the same email address with a source of website occurring after that vendor entry and aggregates the totals for that email adding in the vendor entry totals. While it works for the same data provided, it may not work as desired if multiple vendor entries exist for the same email. Without understanding how the totals should occur or if multiple data exists, or understanding why you need this information based on this data, I can't think of a better option without making lots of assumptions.

SELECT A.Email, sum(B.Amount)+sum(A.Amount) as Total_Amount, 
count(B.time)+count(A.Time) as Transactions
FROM tableName A
INNER join tableName B 
 on A.Email=B.Email 
 AND A.source='vendor'
 AND A.Time < B.Time and B.Source='website'
Group By A.Email

Upvotes: 2

Up_One
Up_One

Reputation: 5271

Your query should look like this :

select email, sum(amount) ,count(*)
from tbl 
 where email='[email protected]'
group by email;

OR - to count all email ! 

select email, sum(amount) ,count(*)
from tbl 
group by email;

All by vendor

    select email, sum(amount) ,count(*)
    from tbl 
 where source ='vendor' 
    group by email;

Also demo here: http://sqlfiddle.com/#!2/de36ed/2

Upvotes: 0

Related Questions