0

I tried to set a left join, but when I did the group by with one column and selected multiple columns I got an SQL error.

This is my query:

SELECT
    b.ClientCode,
    b.LastName, b.FirstName,
    b."Id" AS IdClient,
    c.CaseDate,
    b.Gender,
    b.BirthDate
FROM
    dbo.Clients b
LEFT JOIN
    dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE
    b.ClientCode LIKE '%1%'
    AND DATEDIFF(DAY, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(DAY, '05/06/2017', c.CaseDate) <= 0
GROUP BY
    b.ClientCode
ORDER BY
    b.ClientCode

When I write the query like this:

SELECT
    b.ClientCode,
    b.LastName, b.FirstName,
    b."Id" AS IdClient,
    c.CaseDate,
    b.Gender,
    b.BirthDate
FROM
    dbo.Clients b
LEFT JOIN
    dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE
    b.ClientCode LIKE '%1%'
    AND DATEDIFF(DAY, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(DAY, '05/06/2017', c.CaseDate) <= 0
GROUP BY
    b.ClientCode, b.LastName, b.FirstName, b."Id",
    b.Gender, b.BirthDate, c.CaseDate
ORDER BY
    b.ClientCode

It's working - but it returns duplicate ClientCode...

What can I do?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
kfir
  • 732
  • 10
  • 22
  • 1
    use DISTINCT in the second query, maybe! – DhruvJoshi Jun 12 '18 at 14:56
  • 2
    "i got sql error." - always, ALWAYS provide details of the error. Without it your question could get downvoted and/or closed. – Peter B Jun 12 '18 at 14:57
  • You're trying to group by a single column without an aggregate, what would you expect SQL Server to do with that logic? What's the goal here, to get `DISTINCT` rows? If so, @DhruvJoshi suggestion is pretty clear. – S3S Jun 12 '18 at 14:59
  • The second query can't possibly return true duplicates because you are grouping by all columns. There must be different names/genders/dates for each of the repeated clientcodes you are seeing. Add a snippet of the current output to your question and the output you want to get. – Aaron Dietz Jun 12 '18 at 15:01
  • @AaronDietz i'm betting the OP's definition of duplicate is more than 1 row for each `ClientCode`. Which, as you stated, isn't a true duplicate. – S3S Jun 12 '18 at 15:02
  • I think you need to have a better understanding of what `GROUP BY` really is, instead of testing different grouping sets – LONG Jun 12 '18 at 15:02
  • Duplicate of: https://stackoverflow.com/questions/966176/select-distinct-on-one-column – Tab Alleman Jun 12 '18 at 15:24

1 Answers1

4

You need to put MAX (or MIN or other aggregate function) for your other columns, because of the GROUP BY. Otherwise it doesn't know the value you want.

SELECT b.ClientCode
    ,MAX(b.LastName)
    ,MAX(b.FirstName)
    ,MAX(b."Id") AS IdClient
    ,MAX(c.CaseDate)
    ,MAX(b.Gender)
    ,MAX(b.BirthDate)
FROM dbo.Clients b
LEFT JOIN dbo.ClientCases c ON c.ClientCode = b.ClientCode
WHERE b.ClientCode LIKE '%1%'
    AND DATEDIFF(day, '01/06/2017', c.CaseDate) >= 0
    AND DATEDIFF(day, '05/06/2017', c.CaseDate) <= 0
GROUP BY b.ClientCode
ORDER BY b.ClientCode

I suspect the error you are getting is:

Msg 8120, Level 16, State 1, Line 1
Column 'blah blah' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
sniperd
  • 5,124
  • 6
  • 28
  • 44