CODEWITHSUNDEEP
sqlinformix
Charles Cooper
Charles Cooper

Reputation: 59

Trying to add two queries together

I'm trying to add one more column that is a count for the amount of rows left,
I don't want include rows where my planned quantity matches completed quantity
(plan_qty != cmp_qty)

SELECT   od_f.ob_oid Order,
         Sum(plan_qty) Plan_Units,
         sum(cmp_qty) Completed_Units,
         Round(((Sum(cmp_qty)/sum(plan_qty)) * 100.00),2)Percent_Cmp,
         total_value Value,
         SUM(round(cmp_qty * unit_price,2)) cmp_value
FROM     od_f,
         om_f
WHERE    od_f.ob_oid = om_f.ob_oid
GROUP BY od_f.ob_oid, total_value
ORDER BY Percent_Cmp desc

Heres a query that returns the new column I want

 SELECT   count(od_rid)
 FROM     od_f
 WHERE    od_f.plan_qty != od_f.cmp_qty
 GROUP BY od_f.ob_oid

I can't add the above where statement because it effects my results.
I'm really not sure what to do, to combined these queries a subquery? some sorta union? I'm lost on how to do this.
Thank you for any help

Upvotes: 1

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use conditional aggregation for what you want. Although COUNT() with CASE might be sufficient, my best guess is COUNT(DISTINCT):

SELECT   od_f.ob_oid Order,
         Sum(plan_qty) Plan_Units,
         sum(cmp_qty) Completed_Units,
         Round(((Sum(cmp_qty)/sum(plan_qty)) * 100.00),2)Percent_Cmp,
         total_value Value,
         SUM(round(cmp_qty * unit_price,2)) cmp_value,
         COUNT(DISTINCT CASE WHEN od_f.plan_qty <> od_f.cmp_qty THEN od_f. od_rid END) as newcol
FROM od_f JOIN
     om_f
     ON od_f.ob_oid = om_f.ob_oid
GROUP BY od_f.ob_oid, total_value
ORDER BY Percent_Cmp desc;

You should also learn to use proper, explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.

Upvotes: 1

Ricardo Henriques
Ricardo Henriques

Reputation: 1086

For what I understand you only want a count of rows where plan_qty is not equal to cmp_qty, try this:

SELECT   t.ob_oid                                       AS order,
         SUM(plan_qty)                                  AS plan_units,
         SUM(cmp_qty)                                   AS completed_units,
         ROUND(((SUM(cmp_qty)/SUM(plan_qty)) * 100),2)  AS percent_cmp,
         total_value                                    AS value,
         SUM(ROUND(cmp_qty * unit_price,2))             AS cmp_value,
         SUM(DECODE(plan_qty, cmp_qty, 0, 1))           AS rows_left
FROM     od_f t
         INNER JOIN om_f
            ON t.ob_oid = om_f.ob_oid
GROUP BY t.ob_oid, total_value
ORDER BY percent_cmp DESC;

Upvotes: 0

sagi
sagi

Reputation: 40481

@GordonLinoff solution is good for you, but only if you want to count uniques od_rid , this solution works for both cases, use a correlated query:

SELECT   t.ob_oid Order,
         Sum(plan_qty) Plan_Units,
         sum(cmp_qty) Completed_Units,
         Round(((Sum(cmp_qty)/sum(plan_qty)) * 100.00),2)Percent_Cmp,
         total_value Value,
         SUM(round(cmp_qty * unit_price,2)) cmp_value,
         (SELECT  count(f2.od_rid) FROM od_f f2
          WHERE f2.plan_qty != f2.cmp_qty and f2.ob_oid = f.ob_oid) as newcolumn
FROM     od_f t
         INNER JOIN om_f
ON       t.ob_oid = om_f.ob_oid
GROUP BY t.ob_oid, total_value
ORDER BY Percent_Cmp desc

Upvotes: 1

Related Questions