Select Two query in one query with multiple column

Question

I am getting the issue I want to select two different queries in one query but different column Note: Not like union because union put these two queries in one column

select count(id) as lead,SUM(ol.publisher_earned) as earning from offer_process as ol 
where ol.status='Approved'  and ol.publisher_id='1738' and ol.created_at>='2021-08-01' GROUP by date(ol.created_at)

select count(ol2.id) as clicks from offer_process as ol2  where   ol2.publisher_id='1738'  and ol2.created_at>='2021-08-01' GROUP by date(ol2.created_at)

For union data type must be same. IS SUM(ol.publisher_earned) and count(ol2.id) datatype same or different. If different then UNION will not work. — Rahul Biswas, Sep 01 '21 at 05:45

Rahul Biswas · Accepted Answer · 2021-09-01T06:22:31.700

2

Please check this. If created_date isn't need at SELECT clause then discard it.

SELECT date(created_at) created_at
     , COUNT(publisher_id) clicks
     , SUM(CASE WHEN status='Approved' THEN publisher_earned ELSE 0 END) earning
FROM offer_process
WHERE publisher_id='1738'
    AND created_at>='2021-08-01'
GROUP BY date(created_at)

edited Sep 01 '21 at 06:22

answered Sep 01 '21 at 06:09

Rahul Biswas

3,207
2
10
20

wow you make my day please explain count(1) – Habib Ur Rehman Sep 01 '21 at 06:16
COUNT(1) or COUNT(id) is same. You can use anyone. – Rahul Biswas Sep 01 '21 at 06:18
Welcome @HabibUrRehman for accepting my answer. If possible please upvote my answer along with accepted. Negative vote in an accepting answer looks odd. – Rahul Biswas Sep 01 '21 at 06:30
I can't vote because my reputation is 13 – Habib Ur Rehman Sep 01 '21 at 08:01

score 0 · Answer 2 · answered Sep 01 '21 at 06:08

SELECT earning,clicks FROM
(
    select ol.publisher_id,SUM(ol.publisher_earned) as earning from offer_process as ol 
    where ol.status='Approved'  and ol.publisher_id='1738' and ol.created_at>='2021-08-01' GROUP by date(ol.created_at),ol.publisher_id
)ol
JOIN
(
    select ol2.publisher_id, count(ol2.id) as clicks from offer_process as ol2  
    where ol2.publisher_id='1738'  and ol2.created_at>='2021-08-01' GROUP by date(ol2.created_at),ol2.publisher_id
)ol2 On ol.publisher_id=ol2.publisher_id

the first query give 27 rows individually and the second return 23 but your query return 621 rows — Habib Ur Rehman, Sep 01 '21 at 06:12

Select Two query in one query with multiple column

2 Answers2