Group By with Right Outer Join

Question

need to combine group-by and right-outer-join.

TableA has groups "a", "b", "c", "d"

TableB has: 12|a, 2|b, 1|a 5|b 3|d several times.

I group TableB as view by the group like this:

ViewB: 13|a 7|b 3|d

As you can see, group "c" is not in the TableB and not in the ViewB. I need some outer join like this:

ViewB 13|a 7|b 3|d null|c

how can i achive that?

UPDATE. I see, that this simple example does not describe the complexity. Additionaly i have third Table "Users" So group for each user, for each TableA-Values. Example

userA| 13| a
userA| 7 | b
userA| 3 | d
userB| 10| a
userB| 70 | b
userB| 30 | d

Group "c" is not in the TableB (TableB contains values for the groups) When i try query like this:

select
right outer join TableA
on TableB.id = TableA.id
join TableC
on TableB.other_id = TableC.other_id
group by TableB.groups

I just get something like this:

null| null| c
userA| 13| a
userA| 7 | b
userA| 3 | d
userB| 10| a
userB| 70 | b
userB| 30 | d

But i need:

|userA| 13| a
|userA| 7 | b
|userA| 3 | d
|userA| NULL| c
|userB| 10| a
|userB| 70 | b
|userB| 30 | d
|userB| NULL | c

UPDATE_2 Sorry for less informations. I use oracle g11 Here is the links for example-schema:

http://sqlfiddle.com/#!4/71069

My Sql-Query, which should be extented looks like this:

SELECT usr.user_name,
       SUM (lg_all.new_values) AS group_sum,
       allw.group_name
FROM users usr, products lg_all, myGroup allw
      WHERE usr.user_id = lg_all.user_id 
      AND allw.group_id = lg_all.group_id 
   GROUP BY (usr.USER_ID, allw.group_name)
   ORDER BY usr.LOGONUSER_ID DESC

My result looks like this:

userA| 13| a
userA| 7 | b
userA| 3 | d
userB| 10| a
userB| 70 | b
userB| 30 | d

But i need:

|userA| 13| a
|userA| 7 | b
|userA| 3 | d
|userA| NULL| c
|userB| 10| a
|userB| 70 | b
|userB| 30 | d
|userB| NULL | c

Thx.

Have you tried that right join? – Nenad Zivkovic Aug 01 '17 at 11:15 — Nenad Zivkovic, Aug 01 '17 at 11:15

score 0 · Answer 1 · answered Aug 01 '17 at 11:15

0

You would typically use left join for this:

select a.col1, sum(b.col1)
from tableA a left join
     tableB b
     on a.col1 = b.col2
group by a.col1;

answered Aug 01 '17 at 11:15

Gordon Linoff

1,242,037
58
646
786

Caius Jard · Answer 2 · 2017-08-01T12:56:17.500

If you specifically want to use right join:

SELECT 
  a.col, 
  SUM(b.col) as sumof
FROM 
  TableB b 
  RIGHT JOIN
  TableA a
  ON 
    b.col = a.col
GROUP BY
  a.col;

Right join demands that the "table with solid data" be named after the words "RIGHT JOIN" and the "table with the missing data" be before.. If that's how you prefer to think of things no problems! I tend to use LEFT join all the time probably because I write from left to right, so when I lay my SQL out in my head i think "what are all the solid tables.. and what are the ones with holes... " and I join the "tables with data holes" onto the "tables that are solid" so that the solid data is on the left, and onto it gets joined the data with holes..

i.e. I think left to right. If you think right to left you may prefer your solid table on the right

Update:

You haven't really made it clear how your Users table is structured, but I assume these tables structure:

create table Users (user varchar)
create table tablea (groupname varchar)
create table tableb (groupname varchar, groupcount int)

And hence this query :

SELECT 
  u.user,
  a.groupname, 
  SUM(b.groupcount) as sumof
FROM 
  TableB b 
  RIGHT JOIN
  (
    SELECT * FROM
    TableA a
    CROSS APPLY
    users u
  ) a
  ON 
    b.groupname = a.groupname
GROUP BY
  u.username, a.groupname;

If your users table and tableA are related, please say how. Better still post your tables structure. if nothing else, do the following in SQL Server Management Studio and post a screenshot:

SELECT * FROM users
SELECT * FROM tableA
SELECT * FROM tableB

If the data is confidential, go to SQLFiddle.com and make a representative example, then post the URL to it here

Update2:

Slight problem in that the schema you made doesnt match the example query you posted, but there's a bigger problem: the schema you posted implies that users and groups are NOT related in any way other than via the product table. There's nothing that can be done to give you rows that have a value for user and group other than to mix all users and groups up in a CROSS JOIN and then link products in. You also didn't put any sample data in your fiddle. Did you do Text to DDL? it's pretty neat:

SELECT 
  u.user_name,
  g.group_id,
  SUM(p.product_id) --you don't want to sum the id, but your schema doesnt have anything else!
FROM
  users u
  CROSS JOIN
  mygroup g

  LEFT JOIN
  products p
  ON
    u.user_id = p.user_id AND
    g.group_id = p.group_id

GROUP BY
  u.user_name,
  p.product_id

thx, i have crated a schema and my current sql-query. Pls. look on update_2 — Markus Schnee, Aug 01 '17 at 12:34
Obviously i can´t sum id ;) But the realy important information for me is, that current schema can not provide result, which i need? How i should change my shema? — Markus Schnee, Aug 01 '17 at 13:06
Honestly, I'm not reallly sure, because the naming of things doesn't really let me work out how you're thinking about modelling your data. To my mind, both users and products could be grouped. Clearly it isn't users that are being grouped here, it's products, but then your query says you want a list of all users and groups where the product is null. Now, though a product has a user and a group, there is no relation between users and groups, so why do we demand a query that has to invent user-group pairings in order to discover that no product has a user-group? — Caius Jard, Aug 02 '17 at 05:04

Group By with Right Outer Join

2 Answers2