Need help with GROUP BY function. Getting aggregate error

[BZboy]

Hello all. I am new here and hope someone can help me.



My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.

When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;

I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'


All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.

Is there an alternate route? This is killin me!

thank you in advance

[msoffice.vidyarthi]

It would be good if u zip and attach the db with sample data also explaining the input data and required output data

[BZboy]

It would be good if u zip and attach the db with sample data also explaining the input data and required output data

Hi! here is the SQL from the query:

INSERT INTO [EXP-DATA_DROP] ( [Store Nbr], Store, GBU, [Division Names], [Region Names], Market, City, State, Department, [Traited Items], Outs, GT, [Dept Shelf], BIN1, BIN2, BIN3, [Call Date], [Call Time], [ManG Title], [ManG Name], [Dept: 0%-50%], [Dept: 50%-70%])
SELECT d.[Store Nbr], d.Store, d.GBU, d.[Division Names], d.[Region Names], d.Market, d.City, d.State, d.Department, d.[Traited Items], d.Outs, d.GT, d.[Dept Shelf], a.BIN1, a.BIN2, a.BIN3, s.RESPDATE, s.MaxOfRESPTIME, s.[ManG Title], s.[ManG Name], IIf([Dept Shelf]=Null,Null,IIf([Dept Shelf Availability]>=0 And [Dept Shelf]<0.5,1,Null)) AS Expr1, IIf([Dept Shelf Availability]=Null,Null,IIf([Dept Shelf]>=0.5 And [Dept Shelf]<0.7,1,Null)) AS Expr2
FROM (([EXP-tblOpers] AS d LEFT JOIN [EXP-StoreOps] AS s ON d.[Store Nbr] = s.[Store Nbr]) LEFT JOIN [EXP-tblStoreDEPT] AS t ON (d.[Store Nbr] = t.[Store Nbr]) AND (d.Department = t.DEPT)) LEFT JOIN [impMeasures-Operational] AS a ON s.[Store Nbr] = a.StrNbr;

Here is what I am trying to add (see the end):
INSERT INTO [EXP-DATA_DROP] ( [Store Nbr], Store, GBU, [Division Names], [Region Names], Market, City, State, Department, [Traited Items], Outs, GT, [Dept Shelf], BIN1, BIN2, BIN3, [Call Date], [Call Time], [ManG Title], [ManG Name], [Dept: 0%-50%], [Dept: 50%-70%])
SELECT d.[Store Nbr], d.Store, d.GBU, d.[Division Names], d.[Region Names], d.Market, d.City, d.State, d.Department, d.[Traited Items], d.Outs, d.GT, d.[Dept Shelf], a.BIN1, a.BIN2, a.BIN3, s.RESPDATE, s.MaxOfRESPTIME, s.[ManG Title], s.[ManG Name], IIf([Dept Shelf]=Null,Null,IIf([Dept Shelf Availability]>=0 And [Dept Shelf]<0.5,1,Null)) AS Expr1, IIf([Dept Shelf Availability]=Null,Null,IIf([Dept Shelf]>=0.5 And [Dept Shelf]<0.7,1,Null)) AS Expr2
FROM (([EXP-tblOpers] AS d LEFT JOIN [EXP-StoreOps] AS s ON d.[Store Nbr] = s.[Store Nbr]) LEFT JOIN [EXP-tblStoreDEPT] AS t ON (d.[Store Nbr] = t.[Store Nbr]) AND (d.Department = t.DEPT)) LEFT JOIN [impMeasures-Operational] AS a ON s.[Store Nbr] = a.StrNbr
GROUP BY BIN1, BIN2, BIN3;



Basically, what i am trying to do is this:

Each Store number (Store Nbr) has several departments (Department). These 3 new fields I added: BIN1, BIN2, BIN3 show what the BIN is (Dollar amount) for each store. However, the result set shows the same BIN repeating across all rows for Department. .I just want the BIN grouped by store so it only shows once per store. When it shows up the same for all Departments, it throws off the metrics because it gets added multiple times for each store. It only needs to show once for the store level, not Store & Department.


When I do this, I get the following error:
"You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function"

[msoffice.vidyarthi]

I would be easy if you attach the db with some sample data

and one more thing I believe you have to rewrite the query because 'Store Nbr' is not the only error u might face going forward, u might even face the same prob with other columns

[BZboy]

Thank you again for quick reply. Only thing is, the DB is huge and it is linked to multiple VBA scripts. Any else that I might be able to do to povide additional info?

thanks again

[msoffice.vidyarthi]

Make a copy of original db, remove all other tables which is not referenced in the above said query. which will reduce the db size, zip it and attach it.