Using sum on 2 fields of table with 2 different conditions

phplearner1 · December 28, 2016, 5:08pm

Hi,

I am sure this will be easy sql command for experts but I am just learner so its hard for me how to do this.

I have two tables.

1. Campaign
camp_id
camp_name
order_date
status

2. Visitors
camp_id
date
ip

I want to do the following:

select all campaigns with status “active”
one column with title “Today” with total of all visitors of campaign for today’s date ONLY
one column with title “Total” with total of all visitors of campaign all time

I used this command and its working fine.

SELECT camp.order_date,camp.camp_id,camp.camp_name,count(vis.ip) as Total FROM `campaign` camp JOIN `visitors` vis on camp.camp_id=vis.camp_id where camp.status='Active' and vis.date='2016-12-28' group by camp.camp_id

But I want to add another column which can total all visitors (count(ip)) without date filter.

Please help me how to do this.

astonecipher · December 28, 2016, 5:41pm

Add a nested subquery to grab the new column value. You should also specify the join type, if for no other reason the readability.

phplearner1 · December 28, 2016, 5:51pm

I don’t know how to do what you said, can you add in my command and write here.

astonecipher · December 28, 2016, 5:55pm

No, I can’t. You can do the investigation and I can remove answers from those that wise to deprive you of learning more on the subject.

Nest subqueries in mySQL