
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem with GROUP BY
Hi,
I have some problem with the GROUP BY clause when loading a script.
Table:
LOAD
Week,
Status,
sum(Amount) AS Sales
WHERE Week > 10;
SQL SELECT *
FROM DatabaseName.dbo.TableName GROUP BY Week, Status ORDER BY Week;
My error message is that the first column I select (*) is not grouped by or aggregated, as I don't want to load it into my table. On the other hand, if I do...
SELECT Week, Status, Amount
... I won't accomplish this either.
Thanks for your help in advance!
Best,
Filip
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI
Try like this
LOAD
Week,
Sales,
Status;
Select Week, Status, sum(Amount) As Sales
FROM DatabaseName.dbo.TableName
GROUP BY Week, Status;
Please close the thread by marking correct answer & give likes if you like the post.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Filip,
This is strictly an SQL problem. You can't select any fields in a GROUP BY query unless they are either part of the GROUP BY list, or aggregated in some function or another (e.g. sum, count, etc).
In your case, you'd probably want to use SELECT Week, Status, sum(Amount).
Alternatively, you can load the full data (without a GROUP BY clause) and group it in QV's load statement, but you'll still have to aggregate Amount.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your quick answer.
OK, I have managed to accomplish this in an original SQL database manager.
Is it correct that the problem is that I load and select different things in the script I provided? In "classic SQL" I just have to write...
Select Week, Status, sum(Amount) As Sales
FROM DatabaseName.dbo.TableName
GROUP BY Week, Status
... and I get the result set I want to get.
Can you please give me a code example of a possible solution?
Thank you very much!
/ Filip

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI
Try like this
LOAD
Week,
Sales,
Status;
Select Week, Status, sum(Amount) As Sales
FROM DatabaseName.dbo.TableName
GROUP BY Week, Status;
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Filip,
You have misplaced the group by wrongly. Your script should be like this
Table:
LOAD
Week,
Status,
sum(Amount) AS Sales
WHERE Week > 10 group by Week, Status;
SQL SELECT *
FROM DatabaseName.dbo.TableName ORDER BY Week

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot. It works.
In other words, it's preferred to make the aggregations in the select part, while using GROUP BY...
