CODEWITHSUNDEEP
sqlpostgresqlselectgroup-by
t9217
t9217

Reputation: 117

SQL - group by query from diferent select on same query

I'm trying to group by results by id but It's not working I still get duplicate rows on my return... Here is my query:

SELECT firstTable.id     as id,
   secondTable.holder as holder

FROM (select tb3.id as id
  from table1 tb1
         inner join table2 tb2 on tb1.tb2_id = tb2.id
         inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
         inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
  group by tb3.id) as firstTable,
 (select id_holder,
         sum(temporaryTable.holder) as holder
  from (
         select (select cast(tb4.helper as integer)) as helper,
                count(distinct tb4.id)               as holder,
                tb3.id                               as id_holder
         from table1 tb1
                inner join table2 tb2 on tb1.tb2_id = tb2.id
                inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
                inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
         group by tb3.id, tb4.helper
       ) as temporaryTable
  where temporaryTable.helper between 7 and 8
  group by id_holder) as secondTable

Upvotes: 1

Views: 40

Answers (1)

Barmar
Barmar

Reputation: 780688

You need a joining condition so you don't get a full cross product between the two queries.

SELECT firstTable.id as id,
       secondTable.holder as holder
FROM (
    select tb3.id as id
    from table1 tb1
    inner join table2 tb2 on tb1.tb2_id = tb2.id
    inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
    inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
    group by tb3.id) as firstTable
JOIN (
    select id_holder,
           sum(temporaryTable.holder) as holder
    from (
        select cast(tb4.helper as integer) as helper,
               count(distinct tb4.id) as holder,
               tb3.id as id_holder
        from table1 tb1
        inner join table2 tb2 on tb1.tb2_id = tb2.id
        inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
        inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
        group by tb3.id, tb4.helper
    ) as temporaryTable
    where temporaryTable.helper between 7 and 8
    group by id_holder) as secondTable
ON firstTable.id = temporaryTable.id_holder

Upvotes: 2

Related Questions