0

I'm trying to make a count on SQL and use this count to do a calculation using a information for each row. Per example, use the list value and divide this number for the total of lists of the query.

I can do that but the only way I know is repeating the same JOINs, what is not good because the original query is very complex, have some filters, etc.

Something like this works, but have the JOIN duplication problem:

select distinct list.id, (select count(*) as counter from list l
        JOIN order ord ON ord.list_id = l.id
        JOIN item_order io ON ord .id = io .order_id)
        -- another list informations, subselects
FROM list l
JOIN order ord ON ord.list_id = l.id
JOIN item_order io ON ord .id = io .order_id

I would like to not repeat the JOINs:

JOIN order ord ON ord.list_id = l.id
JOIN item_order io ON ord .id = io .order_id

Data example of list:

id          date      
40337422    2018-01-01
40338569    2018-01-01
403469639   2018-01-01
403469737   2018-01-01
403469781   2018-01-01

Data example of order:

id          list_id
12          40337422
13          40337422
14          403469781

Data example of item_order:

id          order_id
20          12
21          12
22          14
26          14
28          13

Desired results (if the final query have 5 lists... 5 on the count):

id          count
40337422    5
40338569    5
403469639   5
403469737   5
403469781   5

Seems very simple, but I can't figure out how do this in a nice way.

Thanks!

Dherik
  • 17,757
  • 11
  • 115
  • 164

1 Answers1

2

I am guessing that you want this:

SELECT l.id, COUNT(*)
FROM order o
     ON ord.list_id = l.id JOIN
     item_order io
     ON o.id = io.order_id
GROUP BY l.id;

But your logic and desired results are more like this:

SELECT l.id, SUM(COUNT(*)) OVER ()
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If I have a lot of other elements on `SELECT`, I will need to include all of them on the group by, right? Is there another way (using subselect) to not do this? The original query have like 10 elements on SELECT and some of them are subselects. If necessary, I can open another question with more details and accept your answer – Dherik May 15 '18 at 20:46
  • The query does not have the `FROM` clause and the `JOIN`. I tried this query making the corrections, but the count is not the same, because the count is returning the number of item_order of each list, not the counter of all lists on the query. – Dherik May 15 '18 at 23:02