Solved

Excel SQL queries syntax group by

Posted on 2013-06-28
6
694 Views
Last Modified: 2013-07-03
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
0
Comment
Question by:meniyk
  • 2
6 Comments
 
LVL 13

Assisted Solution

by:jonnidip
jonnidip earned 200 total points
ID: 39286291
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
 
LVL 40

Accepted Solution

by:
Sharath earned 300 total points
ID: 39286295
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
 

Author Comment

by:meniyk
ID: 39286307
Hi Sharath_123 and Jonnidip,
thank you for your response. Finally got it. the point has been reward accordingly.
:)

regards
YK
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39286312
Thanks for closing the question but what is the reason for B rating?
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now