?
Solved

Excel SQL queries syntax group by

Posted on 2013-06-28
6
Medium Priority
?
705 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 800 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 1200 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

Industry Leaders: 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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