CODEWITHSUNDEEP
sqlpostgresql
user9222666
user9222666

Reputation:

How to perform subtraction in postgres

I have the below two queries one is giving me total parts as output and the other is giving me total dispatched parts as output.My aim is to get total pending parts which will be given by "total parts - dispatched parts" . how i will get this result directly from the queries.

code:

--total parts

select
    dispatches.id,
    count(parts.id) as total_parts
from dispatch_projects,dispatches,parts 
where
    dispatches.id=dispatch_projects.dispatch_id and
    parts.project=dispatch_projects.project and
    parts.ignore=false and
    dispatch_projects.deleted = false and
    dispatches.created > now() - interval '15 days'
group by  dispatches.id
order by dispatches.id

--total dispatched parts




select
    dispatches.id,
    count(parts.id) as dispatched_parts
from dispatch_projects,dispatches,parts,boxes
where
    dispatches.id=dispatch_projects.dispatch_id and
    parts.project=dispatch_projects.project and
    parts.box_id=boxes.id and
    boxes.project=dispatch_projects.project and
    parts.ignore=false and
    dispatch_projects.deleted = false and
    dispatches.created > now() - interval '15 days'
group by  dispatches.id
order by dispatches.id

Upvotes: 2

Views: 98

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

We can achieve the result you want in a single query.

Appreciate that your second query differs mainly from the first by an extra join to the boxes table. As a result, the term count(parts.id), which actually did count the number of parts in the first query, will now count the number of boxes in the second query. However, we can still obtain the total parts in the second query by taking count(distinct parts.id). This works because it will ignore any duplication on the parts which might have happened due to joining with the boxes table. Consider the following query:

SELECT
    d.id,
    COUNT(DISTINCT p.id) - COUNT(p.id) as result
FROM dispatch_projects dp
INNER JOIN dispatches d
    ON d.id = dp.dispatch_id
INNER JOIN parts p
    ON p.project = dp.project
INNER JOIN boxes b
    ON p.box_id = b.id AND
       b.project = dp.project
WHERE
    p.ignore = false AND
    dp.deleted = false AND
    d.created > now() - INTERVAL '15 days'
GROUP BY
    d.id
ORDER BY
    d.id;

Note that I have replaced your comma separated joins with explicit inner joins. This is considered the preferred syntax.

Upvotes: 1

DTown
DTown

Reputation: 141

You could put these in common table expressions and then calculate the difference between total_parts and total_dispatched parts selected from the join between tmp_total_parts and tmp_total_dispatched_parts joined on the dispatches.id value.

WITH tmp_total_parts AS (
    -- your total_parts query
),

tmp_total_dispatched_parts AS (
    -- your total_dispatched_parts query
),

select 
     id
    ,total_parts - dispatched_parts
from tmp_total_parts
join tmp_total_dispatched_parts
    on tmp_total_parts.id = tmp_total_dispatched_parts.id

Upvotes: 0

Related Questions