Codeignier---sql database [duplicate]

Question

[Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'TantraBackup00.GuildName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT 
    PK.KillerAccountName as CharacterName 
    ,count(PK.KillerAccountName) as totalkillings
    , TB.GuildName 
FROM PlayerKills as PK 
       LEFT JOIN 
    TantraBackup00 as TB ON PK.KillerAccountName = TB.CharacterName 
WHERE 
    PK.KillerCharacterName !='' 
GROUP BY PK.KillerAccountName

Answer 1

To your question in the comments "I did not get MIN(TB.GuildName), what the reson behind it, will you please explain?":

It's required to use an aggregate function like MIN() because you're using a GROUP BY clause only on the KillerAccountName field.

Think of it this way, when you GROUP BY a field, all rows that share the same value of that field get collapsed into a single row. So you may ask, what then happens to the data in the other columns of those rows that have different values from each other between different rows? The answer is, you are unable to SELECT that data now because it's not possible to show more than one value of a column in a single row.

The only way it's possible again is to apply an aggregation function like nbk's answer does when he uses MIN(TB.GuildName) which gives you the first value (of the ordered set of values) in that column. (You could also use the MAX() aggregate function to get last value instead.)

Alternatively you can also include the TB.GuildName column in your GROUP BY clause which would then allow you to directly SELECT it since now every row will have a unique TB.GuildName in it too like so:

SELECT 
    PK.KillerAccountName as CharacterName 
    ,count(PK.KillerAccountName) as totalkillings
    , TB.GuildName
FROM PlayerKills as PK 
       LEFT JOIN 
    TantraBackup00 as TB ON PK.KillerAccountName = 
TB.CharacterName 
WHERE 
    PK.KillerCharacterName !='' 
GROUP BY PK.KillerAccountName, TB.GuildName

Note by adding the TB.GuildName field to the GROUP BY clause above, you may end up with more records than just grouping on PK.KillerAccountName since you're now telling SQL Server to collapse the rows on more specific criteria, the PK.KillerAccountName and TB.GuildName. So whether you want it to GROUP BY both columns so you can see all unique values of TB.GuildName or you don't care specifically which TB.GuildName is shown, as much, and the first (MIN()) one is sufficient, then you'd only want to group on PK.KillerAccountName. That's for you to decide based on your goals.

Answer 2

Every column in the select has to be in the Group by or Have a aggregation Function

It is dewscribed in the manual

SELECT 
    PK.KillerAccountName as CharacterName 
    ,count(PK.KillerAccountName) as totalkillings
    , MIN(TB.GuildName)
FROM PlayerKills as PK 
       LEFT JOIN 
    TantraBackup00 as TB ON PK.KillerAccountName = TB.CharacterName 
WHERE 
    PK.KillerCharacterName !='' 
GROUP BY PK.KillerAccountName

Would be working, of course as we can't see your data, you have to figure it out your self.