Post reply

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

  • June 29, 2012 at 4:31 am

    #271917

    ALTER VIEW [dbo].[neoset_dailySalesManReport]

    AS

    SELECT TOP (100) PERCENT s.entryDate AS 'date', s.customerName, s.customerLocation, sat.title AS 'action type',

    sit.title AS 'invoice type', '€' + convert(nvarchar(max),CONVERT(DECIMAL(10, 2), s.invoiceAmount)) AS 'invoice amount',

    slt.title AS 'furniture location', s.customerPhones, s.mobilePhone, s.InformationPromotions,

    c.lastName + ' ' + c.firstName AS 'salesman',

    convert(nvarchar(max),c.contentid) as 'salesmanid',convert(nvarchar(max),st.contentid) as 'store',

    convert(nvarchar(max),isnull(s.comments,'')) as 'comments',

    --convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)) as 'comments',

    s.relativerecord, s.cultureid

    FROM dbo.neoset_statisticsLangs AS s

    LEFT JOIN dbo.neoset_statisticsActionTypeLangs AS sat ON s.actionType = sat.ContentId AND s.CultureId = sat.CultureId

    INNER JOIN dbo.neoset_statisticsInvoiceTypesLangs AS sit ON s.invoiceType = sit.ContentId AND s.CultureId = sit.CultureId

    LEFT JOIN dbo.neoset_statisticsLocation_types_jt sltjt on s.contentid = sltjt.ref1id

    INNER JOIN dbo.neoset_statisticsLocationTypesLangs AS slt ON sltjt.ref2id = slt.ContentId

    INNER JOIN dbo.neoset_contactsLangs AS c ON s.salesMan = c.ContentId AND s.CultureId = c.CultureId

    INNER JOIN dbo.neoset_storesLangs as st on s.store = st.contentid AND st.cultureid = s.cultureid

    WHERE s.IsDeleted = 0 and s.isApproved = 1 and c.username like '%tatsis%' and s.cultureid = 69

    group by slt.title ,s.entryDate,s.customerName, s.customerLocation, sat.title,sit.title,s.invoiceAmount,

    s.customerPhones, s.mobilePhone, s.InformationPromotions, c.contentid,st.contentid,c.lastName,c.firstName,

    convert(nvarchar(max), isnull(s.comments,''))

    ORDER BY 'salesman', s.customerName, 'date'

    I get Column 'dbo.neoset_statisticsLangs.RelativeRecord' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause, but I have included it in both select and group by clause...any ideas?

  • June 29, 2012 at 4:42 am

    #1507034

    Add the column to the group by statement or remove the column from the select.

  • June 29, 2012 at 4:44 am

    #1507035

    SQL Server will ignore both the TOP(100 percent) and the ORDER BY. You should reconsider what you are trying to do here.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 29, 2012 at 4:47 am

    #1507038

    sorry guys, my bad...I was I wasnt concentrated enough

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply