Solved

Excel SQL queries syntax group by

Posted on 2013-06-28
6
700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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 41

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 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