Excel SQL queries syntax group by

Hi,

I'm currently working on using the sql queries to query the data from Excel worksheet.

The query work if I don't add the "group by" syntax. The moment I add  " group by", I got the annoying greeting from Excel.


'this code sql query work perfectly
strSQL = "Select [LotNo], [Product], [PackingType], [Power], [Qty], [BinLoc] From [StockCount$] where [BinLoc] <> '0' order by [LotNo] " 

Open in new window


'this sql query does not work in if added with group by
strSQL = "Select [LotNo], [Product], [PackingType], [Power], [Qty], [BinLoc] From [StockCount$] where [BinLoc] <> '0' group by [Power] , [Product] order by [LotNo] "

Open in new window



Best regards
YK
meniykAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SharathConnect With a Mentor Data EngineerCommented:
You need to include all the columns (except the aggregated columns) in the SELECT clause in GROUP BY clause.
This query should work for you, modify the aggregated columns as per your requirement.
strSQL = "Select max([LotNo]) AS [LotNo], [Product], max([PackingType]) AS [PackingType], [Power], sum([Qty]) AS [Qty], MAX([BinLoc]) AS [BinLoc] From [StockCount$] where [BinLoc] <> '0' group by [Power] , [Product] order by [LotNo] "

Open in new window

0
 
jonnidipConnect With a Mentor Commented:
Your query is not a "group by" query.
It's not just to add "group by" at the end. You will need to understand how it works, or better, what does it mean. Please take a look at: http://www.w3schools.com/sql/sql_groupby.asp.
As you can see, the query should return the fields you chose in the group by clause.
If you need to return other fields, then you have to logically know what you need by them.
For example max(, min(, count(...

Regards.
0
 
meniykAuthor Commented:
Hi Sharath_123 and Jonnidip,
thank you for your response. Finally got it. the point has been reward accordingly.
:)

regards
YK
0
 
SharathData EngineerCommented:
Thanks for closing the question but what is the reason for B rating?
0
All Courses

From novice to tech pro — start learning today.