[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel SQL queries syntax group by

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

656 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