CODEWITHSUNDEEP
postgresql
MCM13
MCM13

Reputation: 275

Subtract Results From 2 Queries

I have 2 queries that give same results (same columns, rows) but with different data. I need to subtract the results from one column of query #1 to the same column but from query #2.

This are my queries

QUERY 1

SELECT 
COUNT(DISTINCT CASE WHEN customer.ref IN ('emp', 'oth') THEN customer.id END) as visit_count, locations.name
FROM visits 
LEFT JOIN customer ON customer.id = visits.id
LEFT JOIN locations ON customer.loc_id = locations.id
WHERE visits.group_id = 1
GROUP BY locations.name, locations.id;

RESULTS
visit_count  |   location
---------------------------
      5      |    loc1
      1      |    loc2
      1      |    loc3

QUERY 2

SELECT COUNT(customer.id) as visit_count, locations.name FROM locations 
LEFT JOIN visits ON locations.name = visits.location_check
LEFT JOIN customer ON visits.cust_id = customer.id AND customer.group_id = 1
WHERE locations.group_id = 1 AND locations.active = true
GROUP BY locations.location_name, locations.id;

RESULTS
visit_count  |   location
---------------------------
      10      |    loc1
      15      |    loc2
      18      |    loc3

The results are the same, I just need to substract the first results from the second one. So the result I want would be:

visit_count  |   location
---------------------------
      5      |    loc1
      14     |    loc2
      17     |    loc3

Is there a way to join them together and subtract one column from another?

Upvotes: 2

Views: 666

Answers (1)

richyen
richyen

Reputation: 9968

This is the most straightforward answer:

SELECT b.visit_count-a.visit_count, a.location
FROM
(
  SELECT 
  COUNT(DISTINCT CASE WHEN customer.ref IN ('emp', 'oth') THEN customer.id END) as visit_count, locations.name
  FROM visits 
  LEFT JOIN customer ON customer.id = visits.id
  LEFT JOIN locations ON customer.loc_id = locations.id
  WHERE visits.group_id = 1
  GROUP BY locations.name, locations.id;
) as a
(
  SELECT COUNT(customer.id) as visit_count, locations.name FROM locations 
  LEFT JOIN visits ON locations.name = visits.location_check
  LEFT JOIN customer ON visits.cust_id = customer.id AND customer.group_id = 1
  WHERE locations.group_id = 1 AND locations.active = true
  GROUP BY locations.location_name, locations.id;
) as b
WHERE a.location=b.location;

This is obviously merely a copy/paste of what you provided, but there seem to be some details that need to be ironed out, like customer.id = visits.id in one query, and customer.id = visits.cust_id in the other query.

You seem to want to get the number of total visits at each location, minus the ones where customer.ref is either emp or oth. If that's the case, I would suppose you just want something like this:

SELECT count(*), l.name as location
FROM visits v
JOIN customer c ON c.id=v.id
JOIN locations l ON l.id=c.loc_id
WHERE v.group_id = 1
AND l.active
AND c.ref NOT IN ('emp','oth')
GROUP BY l.name;

Of course, the correct query depends on your table structure, which we don't really know here, as the column names seem not to be consistent.

Disclosure: I work for EnterpriseDB (EDB)

Upvotes: 3

Related Questions