CODEWITHSUNDEEP
sqlselect
user1513073
user1513073

Reputation: 21

combine results from different selects

I have one table that contains a field "ID", "mailSent" and "serviceUsed". "mailSent" contains the time when a mail was sent and "serviceUsed" contains a counter that just says if the user has used the service for the particular mail that I have sent.

I am trying to do a report that gives me back for each ID the following two facts: 1. The last time when a user has used the service, i.e., the time when for a particular user serviceUsed != 0 2. The total number of times a user has used the service, i.e., sum(serviceUsed) for each user

I would like to display this in one view and map the result always to the particular user. I can build each of the two queries separately but do not know how to combine it into one view. The two queries look as follows:

1. Select ID, max(mailSent) from Mails where serviceUsed > 0 group by ID
2. Select ID, sum(serviceUsed) from Mails group by ID

Notice that I cannot just combine them both because I also want to show the IDs that have never used my service, i.e., where serviceUsed = 0. Hence, if I just eliminate the where clause in my first query, then I will get wrong results for max(mailSent). Any idea how I can combine both?

In other words what I want is then something like this: ID, max(mailSent), sum(serviceUsed) where max(mailSent) is from the first query and sum(serviceUsed) from the second query.

Regards!

Upvotes: 0

Views: 49

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You want to do this with conditional aggregation:

select ID, max(case when serviceUsed > 0 then mailSent end),
       sum(serviceUsed)
from Mails
group by ID;

Upvotes: 0

maja
maja

Reputation: 18094

You can write it within one Query:

SELECT ID, sum(serviceUsed), max(mailSent) from Mails group by ID;

The problem, that you don't have the serviceUsed > 0 in your second Query doesn't matter. You can sum them up too, because they have the value 0.

If you have the following input:

 id  serviceUsed  mailSent
--------------------------
1        0        1.1.1970
1        4        3.1.1970
1        3        4.1.1970
2        0        2.1.1970

The Query should return this result:

 id  serviceUsed  mailSent
--------------------------
  1        7      4.1.1970
  2        0      2.1.1970

But I wonder, where your primary key is?

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try like this

SELECT * FROM
(
   Select ID, max(mailSent) from Mails where serviceUsed > 0 group by ID
   UNOIN ALL
   Select ID, sum(serviceUsed) from Mails group by ID 
) AS T

Upvotes: 1

Related Questions