MariaDB comparing number count from two tables

Question

I'm trying to compare the total COUNT of one value in a table with the total COUNT of another, I'm just not sure how to do it properly.

If a SalesID total COUNT in total_sales table is higher than fid total COUNT in total_leads table then I would like to output those values.

Tables:

CREATE TABLE total_leads
    (`uid` int, `tstamp` int, `fid` int, `phone` varchar(2))
;

INSERT INTO total_leads
    (`uid`, `tstamp`, `fid`, `phone`)
VALUES
    (1, 1299795364, 1525, '""'),
    (1, 1309854563, 4031, '""'),
    (1, 1309854562, 3134, '""'),
    (5, 1280752536, 18, '""'),
    (5, 1280942244, 219, '""'),    
    (5, 1309862562, 1234, '""'),
    (5, 1480752536, 184, '""'),
    (5, 1520942244, 119, '""')
;

CREATE TABLE total_sales
    (`UserID` int, `SalesID` int, `Time` varchar(8))
;

INSERT INTO total_sales
    (`UserID`, `SalesID`, `Time`)
VALUES
    (1, 172, '12:57:43'),
    (1, 3563, '15:59:49'),
    (1, 9508, '01:46:47'),
    (5, 18935, '07:26:07'),
    (5, 19378, '08:06:41'),
    (1, 144, '11:52:41'),
    (1, 9248, '02:43:40'),
    (1, 3423, '14:54:45'),
    (5, 11935, '03:21:06'),
    (1, 1448, '05:02:24')
;

MySQL Commands:

SELECT x.uid, COUNT(*), COUNT(DISTINCT x.fid)
FROM total_leads AS x
WHERE x.uid BETWEEN 1 AND 5
GROUP BY x.uid;

SELECT ud.UserId, COUNT(*), COUNT(DISTINCT ud.SalesID)
FROM total_sales AS ud
WHERE ud.UserId BETWEEN 1 AND 5  
GROUP BY ud.UserID;

It returns (total_leads, total_sales):

 uid    COUNT(*)    COUNT(DISTINCT x.fid)
  1        3               3
  5        5               5

UserId  COUNT(*)    COUNT(DISTINCT ud.SalesID)
  1        7               7
  5        3               3

So I'm hoping to get a result such as:

UserId   Sales           Leads   Dif
  1        7               3      4

Even just knowing just which UserID has Sales > fid would be perfectly fine too:

UserId  COUNT(*)    COUNT(DISTINCT ud.SalesID)
  1        7               7

Online Example.

I tried several things such as UNION, but that just merged all the values together.

score 2 · Accepted Answer · answered Feb 15 '19 at 06:36

use subquery join

select uid,leadcount,salescount,salescount-leadcount as diff from 
(
SELECT x.uid, COUNT(*) leadcount, COUNT(DISTINCT x.fid)
FROM total_leads AS x
WHERE x.uid BETWEEN 1 AND 5
GROUP BY x.uid
) t1 left join 
(
SELECT ud.UserId, COUNT(*) salescount, COUNT(DISTINCT ud.SalesID)
FROM total_sales AS ud
WHERE ud.UserId BETWEEN 1 AND 5  
GROUP BY ud.UserID
) t2 on t1.uid=t2.UserId

score 2 · Answer 2 · answered Feb 15 '19 at 06:42

Try this code,

 SELECT x.uid,COUNT(DISTINCT x.fid) AS leads,COUNT(DISTINCT ud.SalesID) AS 
 sales,((COUNT(DISTINCT ud.SalesID))-(COUNT(DISTINCT x.fid)))
 FROM
 total_leads AS X,total_sales AS ud WHERE x.uid=ud.UserId AND ud.UserId BETWEEN 1 AND 5 
 GROUP BY ud.UserID;

score 0 · Answer 3 · answered Feb 15 '19 at 07:27

you can do this by using below method, where you can compare total count of each table

select t1.uid,t2.sales_count,t1.leads_count,
      case when t1.leads_count > t2.sales_count then t1.leads_count -t2.sales_count 
           else t2.sales_count -t1.leads_count
      end as diff
from 
(
select uid,count(fid) as leads_count
from total_leads
group by uid
)t1
inner join 
(
select UserID,count(SalesID)as sales_count
from total_sales
group by UserID
)t2 on t1.uid = t2.userid ;

MariaDB comparing number count from two tables

3 Answers3