Combining 2 tables and outputting top 5 results

[beanymanuk]

Hi

 

I am trying to combine 2 tables and show the 5 highest results

 

SELECT email, total_amount FROM cashsumtotals WHERE cause = '$causename'

AND

SELECT email, total_amount FROM cardsumtotals WHERE cause = '$cause_id_get'

 

I need to get the total SUM of total_amount where email address is the same and ORDER results by total_amount DESC and display the top 5 results

 

Any help would be muchly appreciated.

[Muddy_Funster]

I'm confused, you said you wanted to use two tables, your example only shows one.

[kickstart]

Hi

 

Also a touch confused, but if you did mean 2 items from the same table something like this:-

 

SELECT email, SUM(total_amount) 
FROM cashsumtotals 
WHERE cause = '$causename' 
OR cause = '$cause_id_get'
GROUP BY email
ORDER BY SUM(total_amount) DESC
LIMIT 5

 

All the best

 

Keith

[beanymanuk]

I have 2 tables though?

 

cashsumtotals

AND

cardsumtotals

 

Eg if have

 

in cardsumtotals

email                              total_amount

Dan@???.com                          16.66

 

in cashsumtotals

email                              total_amount

Dan@???.com                         10

lisa.evans@?????.com              10

 

result to be

 

email                                  total_amount

Dan@???.com                              26.66

lisa.evans@???.com                    10

[beanymanuk]

Found a solution in the end

Is this the most efficient way of doing it though?

 

SELECT email, SUM(sumAmount1) as total
FROM (
  SELECT email, SUM(total_amount) as sumAmount1
      FROM cashsumtotals
      WHERE cause= '$causename'
      GROUP BY email
  UNION ALL SELECT email, SUM(total_amount)
      FROM cardsumtotals
      WHERE cause = '$cause_id_get'
      GROUP BY email
  ) tmp
GROUP BY email

[kickstart]

Hi

 

Ah, missed the slightly different names.

 

Doesn't seem to be any point to summing things separately, so try the following.

 

SELECT email, SUM(total_amount) as total
FROM (
  SELECT email, total_amount
      FROM cashsumtotals
      WHERE cause= '$causename'
  UNION ALL SELECT email, total_amount
      FROM cardsumtotals
      WHERE cause = '$cause_id_get'
  ) tmp
GROUP BY email 

 

All the best

 

Keith

[kickstart]

Hi

 

Or if you have a table of people then maybe this (ie a table that the email address links to), although not tested and not sure that adding the 2 SUMs together will work OK.

 

SELECT a.email, SUM(b.total_amount) + SUM(c.total_amount)
FROM People a
LEFT OUTER JOIN cashsumtotals b ON a.email = b.email AND b.cause= '$causename'
LEFT OUTER JOIN cardsumtotals c ON a.email = c.email AND c.cause = '$cause_id_get'
GROUP BY a.email
ORDER BY SUM(b.total_amount) + SUM(c.total_amount) DESC
LIMIT 5

 

All the best

 

Keith

[beanymanuk]

Hi

 

How can I show the total_amount for each of the Select queries as well as the name, email, SUM(total_amount) that it already displays.

 

SELECT name, email, SUM(total_amount) as total
FROM (
  SELECT name, email, total_amount
      FROM cashsumtotals
      WHERE cause= '$causename'
  UNION ALL SELECT name, email, total_amount
      FROM cardsumtotals
      WHERE cause = '$cause_id_get'
  ) tmp
GROUP BY email ORDER BY total DESC LIMIT 6

[fenway]

Don't start a new thread for the same issue.

 

kickstart gave you the answer -- and a better way to do this anyway.

[beanymanuk]

I havn't got a table called People or like people that kickstart suggested.

[kickstart]

Hi

 

In which case use a subselect to get one:-

 

SELECT a.email, SUM(b.total_amount) + SUM(c.total_amount)
FROM (SELECT DISTINCT email FROM  cashsumtotals UNION SELECT DISTINCT email FROM  cardsumtotals ) AS a
LEFT OUTER JOIN cashsumtotals b ON a.email = b.email AND b.cause= '$causename'
LEFT OUTER JOIN cardsumtotals c ON a.email = c.email AND c.cause = '$cause_id_get'
GROUP BY a.email
ORDER BY SUM(b.total_amount) + SUM(c.total_amount) DESC
LIMIT 5 

 

All the best

 

Keith

[beanymanuk]

I tried the above but this doesnt pull out the correct emails addresses and the total_amount comes out as NULL

So that is not doing what I want

[kickstart]

Hi

 

Can't see how it can bring back the wrong emails, except for just bringing back a random 5 because the total is null.

 

Export your table declares and sample data then I can at least try out the code I suggest.

 

All the best

 

Keith

[beanymanuk]

SELECT name, email, SUM(total_amount) as total
FROM (
  SELECT name, email, total_amount
      FROM cashsumtotals
      WHERE cause= '1st Lawford Scout Group'
  UNION ALL SELECT name, email, total_amount
      FROM cardsumtotals
      WHERE cause = '53'
  ) tmp
GROUP BY email ORDER BY total DESC LIMIT 6

 

pull out these results

 

name email total

Daniel A [email protected] 16.66

Peter G [email protected] 5

 

The 16.66 in this example is made up from just cardsumtotals

The 5 in this example is made up from just cashsumtotals

But I want to be able to to show how much some from cardsumtotals and how much come from cashsumtotals for each email.

[fenway]

Then don't use the plus sign.

[beanymanuk]

SELECT a.email, SUM(b.total_amount), SUM(c.total_amount)
FROM (SELECT DISTINCT email FROM  cashsumtotals UNION SELECT DISTINCT email FROM  cardsumtotals ) AS a
LEFT OUTER JOIN cashsumtotals b ON a.email = b.email AND b.cause= '1st Lawford Scout Group'
LEFT OUTER JOIN cardsumtotals c ON a.email = c.email AND c.cause = '53'
GROUP BY a.email
ORDER BY SUM(b.total_amount) + SUM(c.total_amount) DESC
LIMIT 5 

 

Result

[email protected] NULL NULL

[email protected] NULL NULL

[email protected] NULL NULL

[email protected] NULL NULL

[email protected] NULL NULL

[kickstart]

Hi

 

Which suggests that there are no records that match those causes.

 

If you can post the table layouts and sample data then we can try and do some useful checking. Without that we are just guessing

 

All the best

 

Keith