Can't group by with two inner joins

Question

this is the query without group by and it is showing data perfectly

SELECT 
    [b].[FullName]
  , STUFF(
       (SELECT 
            ','+CONVERT(VARCHAR(30), [t].[pdate], 120)
        FROM [DBTrans] [t]
        WHERE [t].[dbID] = [uk].[ID] FOR
        XML PATH('')), 1, 1, '') AS [ins_Dates]
  , [InvNo]
  , [AdCaption]
  , CONCAT([AdCM], 'x', [AdCOL]) AS [SIZE]
  , [NetAmt]
  , [RecievedAmount]
  , [NetAmt] - [RecievedAmount] AS [O_S]
FROM [DailyBooking] [uk]
INNER JOIN [DBTrans]
     ON [uk].[ID] = [DBTrans].[dbID]
INNER JOIN [Publication] [b]
     ON [uk].[AdPub] = [b].[ID]
WHERE [b].[FullName] LIKE '%a%';

while when i try to group by for for some purpose it showing different errors on different changes

for Example :

Msg 8120, Level 16, State 1, Line 2 Column 'DailyBooking.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT 
    [b].[FullName]
  , STUFF(
       (SELECT 
            ','+CONVERT(VARCHAR(30), [t].[pdate], 120)
        FROM [DBTrans] [t]
        WHERE [t].[dbID] = [uk].[ID] FOR
        XML PATH('')), 1, 1, '') AS [ins_Dates]
  , [InvNo]
  , [AdCaption]
  , CONCAT([AdCM], 'x', [AdCOL]) AS [SIZE]
  , [NetAmt]
  , [RecievedAmount]
  , [NetAmt] - [RecievedAmount] AS [O_S]
FROM [DailyBooking] [uk]
INNER JOIN [DBTrans]
     ON [uk].[ID] = [DBTrans].[dbID]
INNER JOIN [Publication] [b]
     ON [uk].[AdPub] = [b].[ID]
WHERE [b].[FullName] LIKE '%a%'
GROUP BY 
    [b].[FullName];

The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. — jarlh, Jan 29 '18 at 09:59
Possible duplicate of [cross tabulation Msg 8120, Level 16, State 1, Line 8](https://stackoverflow.com/questions/19790600/cross-tabulation-msg-8120-level-16-state-1-line-8) — Nick.Mc, Jan 29 '18 at 10:03
I suggest you take the very basic step of searching on Msg 8120 — Nick.Mc, Jan 29 '18 at 10:03
i have alredy tried my level best on it thats why i post a question @Nick.McDermaid — Syed Muhammad Munis Ali, Jan 29 '18 at 10:04
Have you tried adding `DailyBooking.ID` to the `GROUP BY`? it is being used in the SELECT (through `uk.ID`) — Peter B, Jan 29 '18 at 10:06
It's logical and basic topic of Group by. each table or view column in any nonaggregate expression in the — Başar Kaya, Jan 29 '18 at 10:08
yeah i have tried @PeterB ! then it says the same error on invNo — Syed Muhammad Munis Ali, Jan 29 '18 at 10:09
when i try to put each column in GROUP BY it does not show any error but it just show the same records as before Group By but just in a perfect order problem is i want to group by with just a single coulmn name [b].[FullName] @BaşarKaya — Syed Muhammad Munis Ali, Jan 29 '18 at 10:20
It seems like your question has changed. You've resolved the group by error now you have another problem. — Nick.Mc, Jan 29 '18 at 10:21
You should clarify your questions (in general) by being sure that every column has a table alias. We don't know what your data looks like. — Gordon Linoff, Jan 29 '18 at 10:23
Look at the first comment + the one after mine. They mean: if you use GROUP BY, then any column **NOT** present in the GROUP BY clause **MUST** have an aggregation expression such as `COUNT()`, `SUM()`, `MAX()` or `AVG()`. As you long as you don't follow that rule, the error will stay. — Peter B, Jan 29 '18 at 10:23
If you want to group by one column then you need to decide what to do with all the other data. For example you could just put a `max` function around all the other columns but I don't think that's what you want. Again.... the question I linked to and a very basic search will tell you that — Nick.Mc, Jan 29 '18 at 10:23
Let Me give it a try thanks for that much brief description both of you — Syed Muhammad Munis Ali, Jan 29 '18 at 10:25
i understand @PeterB but problem is i just cant put any another function for the other data i had perform everything i wanted except the group by now not getting what should i perform with other data for processing group by please help — Syed Muhammad Munis Ali, Jan 29 '18 at 10:34
If all you want is ORDER BY then just use that... GROUP BY may not be what you need to use, or else, you need find a way that involves aggregate functions. If you don't understand why that is, then you'll need to look for a tutorial. — Peter B, Jan 29 '18 at 10:44
thank you so much @PeterB i m already much nearer by your suggestions thanks again :) — Syed Muhammad Munis Ali, Jan 29 '18 at 10:46
Put it this way.. do you want less rows returned? If a given `FullName` has three different `InvNo`, which `InvNo` do you want to show? I think if you take the time to show sample data you'll make more progress because no one understands what you're trying to explain in words. — Nick.Mc, Jan 29 '18 at 12:04
i have sort out my problem by my own but now dont know what should do for this question :p @Nick.McDermaid — Syed Muhammad Munis Ali, Jan 29 '18 at 12:09
Learn from it - next time post sample data! Normally I would suggest posting an answer but we still don't understand the question. But your dilligence is appreciated — Nick.Mc, Jan 29 '18 at 12:19

Shahbaz Hussain · Answer 1 · 2018-01-29T15:32:57.747

Assuming the other columns are functionally dependent on the grouping column, the simplest answers are either

a. Group by the other columns as well:

    Select r.*
FROM
(SELECT 
    [b].[FullName]
  , STUFF(
       (SELECT 
            ','+CONVERT(VARCHAR(30), [t].[pdate], 120)
        FROM [DBTrans] [t]
        WHERE [t].[dbID] = [uk].[ID] FOR
        XML PATH('')), 1, 1, '') AS [ins_Dates]
  , [InvNo]
  , [AdCaption]
  , CONCAT([AdCM], 'x', [AdCOL]) AS [SIZE]
  , [NetAmt]
  , [RecievedAmount]
  , [NetAmt] - [RecievedAmount] AS [O_S]
FROM [DailyBooking] [uk]
INNER JOIN [DBTrans]
     ON [uk].[ID] = [DBTrans].[dbID]
INNER JOIN [Publication] [b]
     ON [uk].[AdPub] = [b].[ID]
WHERE [b].[FullName] LIKE '%a%') r
GROUP BY 
    [r].[FullName],
    [r].ins_Dates,
    [r].InvNo,
    [r].AdCaption,
    [r].SIZE,
    [r].NetAmt,
    [r].RecievedAmount,
    [r].O_S

or

b. Use an aggregate function such as max:

SELECT 
        max([b].[FullName])
      , STUFF(
           (SELECT 
                ','+CONVERT(VARCHAR(30), [t].[pdate], 120)
            FROM [DBTrans] [t]
            WHERE [t].[dbID] = [uk].[ID] FOR
            XML PATH('')), 1, 1, '') AS [ins_Dates]
      , max([InvNo])
      , max([AdCaption])
      , CONCAT([AdCM], 'x', [AdCOL]) AS [SIZE]
      , max([NetAmt])
      , max([RecievedAmount])
      , [NetAmt] - [RecievedAmount] AS [O_S]
    FROM [DailyBooking] [uk]
    INNER JOIN [DBTrans]
         ON [uk].[ID] = [DBTrans].[dbID]
    INNER JOIN [Publication] [b]
         ON [uk].[AdPub] = [b].[ID]
    WHERE [b].[FullName] LIKE '%a%'

your first solution is throwing the same error as mine but on the AdCM and AdCOL columns which were concat after AdCaption — Syed Muhammad Munis Ali, Jan 29 '18 at 10:38
sorry you dont actually need to add max to first solution. The first solution can list all the selected columns in the group by clause and it should work. — Shahbaz Hussain, Jan 29 '18 at 11:34

Can't group by with two inner joins

1 Answers1