0

This is my sql query:

select 
    sum(table1.quantity) as t1q, 
    sum(table1_2.quantity) as t2q, 
    sum(table3.quantity) as t3q, 
    table1.pid as pid
from table1 
    inner join table3 on table1.pid=table3.pid
    inner join table1 table1_2 on table1.pid=table1_2.pid 
where 
    table1.to_id=10 and 
    table3.some_id=10 and 
    table1_2.from_id=10 
group by pid;

Sample Data:

Table1:
quantity, to_id, from_id, pid

6, 10, 999999, 345
4, 888999, 10, 345
3, 888999, 10, 345

If you observer above sql:

There are 2 tables: 

Table1 (same table used twice as table1 and table1_2)
Table3

I want to fetch from table1.to_id=10 for calculating t1q and table1(same table).from_id=10 for calculating t2q.

I am getting correct output in some cases but in some cases, t1q is giving value as 12 when it should be 6. In that scenario, its because after joins, there are 2 records of table1_2 and only one record for for table1. So, it is counting table1.quantity twice even though its has only one record after filtering.

Can you please provide correct sql query.

Improve this question
8
  • 1
    Can you include sample data and desired output? Commented Apr 29, 2019 at 15:21
  • Added sample data. If you observe, there is only one record in table1 with to_id=10 but 2 records with from_id=10. Also, I just changed pid to 345 in query. Commented Apr 29, 2019 at 15:28
  • 1
    This means that there are multiple records in table1 where from_id = 10. Is there some other field in table1 that you can use in your where clause to restrict the results to only returning one record from inner join operation? Commented Apr 29, 2019 at 15:29
  • There may be multiple records when filtering with from_id. and when filtering with to_id also. Only in above data, to_id filtering has one row. The problem is, to_id filtering might have like 5 records and from_id might have 8 records. Then, to_id will also consider 8 records which is problem here. Commented Apr 29, 2019 at 15:45
  • So basically, the requirement is: For every pid, calculate t1q, t2q, t3q where t1q is the sum of qty of records when table1.to_id=10, t2q is sum of qty of records when table1_2.from_id=10, t3q is sum of qty of records when table3.some_id=10. How can we achieve this? If this is not possible, should I use cron to prepare intermediate table with data calculated with forloops. Commented Apr 29, 2019 at 15:51

3 Answers 3

Reset to default
1

I'm presupposing that you are correct, that the ONLY problem is when the join on table1_2 causes duplication.

In which case, aggregate that table first, before joining on it.

select 
    sum(table1.quantity) as t1q, 
    sum(table1_2.quantity) as t2q, 
    sum(table3.quantity) as t3q, 
    table1.pid as pid
from
    table1 
inner join
    table3
        on table1.pid=table3.pid
inner join
(
    SELECT
        pid,
        SUM(quantity) AS quantity
    FROM
        table1
    WHERE
        from_id = 10
    GROUP BY
        pid
) 
    table1_2
        on table1.pid=table1_2.pid 
where 
    table1.to_id=10 and
    table3.some_id=10
group by
    pid

If your assumption is incomplete, you may need to do that to each table...

select 
    table1.quantity    as t1q, 
    table1_2.quantity  as t2q, 
    table3.quantity    as t3q, 
    table1.pid         as pid
from
 (
    SELECT
        pid,
        SUM(quantity) AS quantity
    FROM
        table1
    WHERE
        to_id = 10
    GROUP BY
        pid
)
    table1
inner join
(
    SELECT
        pid,
        SUM(quantity) AS quantity
    FROM
        table3
    WHERE
        some_id = 10
    GROUP BY
        pid
)
    table3
        on table1.pid=table3.pid
inner join
(
    SELECT
        pid,
        SUM(quantity) AS quantity
    FROM
        table1
    WHERE
        from_id = 10
    GROUP BY
        pid
) 
    table1_2
        on table1.pid=table1_2.pid

Without and example dataset to test against, there's a great deal of supposition there.

But, as a rule, aggregate before joining, if you're joining on a column that's not unique in that table.

EDIT: Response to comment

SELECT
    COALESCE(table1.t1q, 0)           AS t1q,
    COALESCE(table1.t2q, 0)           AS t2q,
    COALESCE(table3.t3q, 0)           AS t3q,
    COALESCE(table1.pid, table3.pid)  AS pid
FROM
(
    SELECT
        pid,
        SUM(CASE WHEN   to_id = 10 THEN quantity ELSE 0 END) AS t1q,
        SUM(CASE WHEN from_id = 10 THEN quantity ELSE 0 END) AS t2q
    FROM
        table1
    WHERE
            to_id   = 10
        OR  from_id = 10
    GROUP BY
        pid
)
    table1
FULL OUTER JOIN
(
    SELECT
        pid,
        SUM(quantity) AS quantity
    FROM
        table3
    WHERE
        some_id = 10
    GROUP BY
        pid
)
    table3
        ON table1.pid = table3.pid

Or...

SELECT
    SUM(t1q)   AS t1q,
    SUM(t2q)   AS t2q,
    SUM(t3q)   AS t3q,
    pid
FROM
(
    SELECT pid, quantity AS t1q, 0 AS t2q, 0 AS t3q FROM table1 WHERE to_id = 10
    UNION ALL
    SELECT pid, 0              , quantity, 0        FROM table1 WHERE from_id = 10
    UNION ALL
    SELECT pid, 0              , 0       , quantity FROM table3 WHERE some_id = 10
)
    combined
GROUP BY
    pid
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your second answer is working, thanks. But it is ignoring pids when ateast one of t1q or t2q or t3q is 0. Can we get output in situations like 1, 0, 0 or 5, 0, 2 or 0, 5, 1 ?
1

I think the issue comes from trying to use the same table twice in the same calculation, where you have no unique keys.

This is possible, but the SQL is ugly. The way I would approach it would be like this, assuming your "table3", (which you don't specify) is laid out similarly to your "table1":

SELECT a.pid,
       b.t1q,
       c.t2q,
       d.t3q
 FROM (SELECT DISTINCT(pid) FROM table1 AS pid) a
 JOIN (SELECT pid, SUM(quantity) AS t1q FROM table1 WHERE to_id = 10 GROUP BY pid) b ON a.pid = b.pid
 JOIN (SELECT pid, SUM(quantity) AS t2q FROM table1 WHERE from_id = 10 GROUP BY pid) c ON a.pid = b.pid
 JOIN (SELECT pid, SUM(quantity) AS t3q FROM table3 WHERE some_id = 10 GROUP BY pid) d ON a.pid = d.pid
Improve this answer

4 Comments

Sorry, didnt work for me. Is there a way to use "case" as per this example - stackoverflow.com/questions/30161042/…
Seems like the other answer is working. Anyway thanks for your help. May be your answer is right but for me, product_id repeated multiple times.
Oh, so you said my solution "didn't work" without actually trying it? That's cool... The other solution with case when works as well, but is slower to execute and a lot more complex than mine. But whatever. There are always multiple ways to solve a problem with SQL. I believe my solution is the most elegant for your issue
I tried it immediately after you answered. When I tried, I got same pid repeated multiple times. Anyway I will try it again. Plus one for you. Thanks.
0

I don't have enough information to determine whether your schema is 'wrong', and that wasn't your original question.

However, with the information you've provided, this is how I would approach solving the original question:

SELECT t1.pid, t1q, t2q, t3q

FROM (SELECT pid, to_id, SUM(quantity) AS t1q FROM table1 GROUP BY pid, to_id) AS t1

INNER JOIN (SELECT pid, from_id, SUM(quantity) AS t2q FROM table1 GROUP BY pid, from_id) AS t2
ON t1.pid = t2.pid AND t1.to_id = t2.from_id

INNER JOIN (SELECT pid, some_id, SUM(quantity) AS t3q FROM table3 GROUP BY pid, some_id) AS t3
ON t1.pid = t3.pid AND t1.to_id = t3.some_id

WHERE t1.to_id = 10

You can then udpate t1.to_id = 10 to be any value you want in future queries, and it may even lend itself to becoming a stored procedure where you pass in the to_id as an argument.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.