Solved

Can't seem to GROUP BY and ORDER BY in sql query

Posted on 2011-09-02
13
300 Views
Last Modified: 2012-05-12
I'm have a sql query to an oracle database that I can either ORDER BY or GROUP BY without issue but if I try to do both I get this error: "ORA-00979: not a GROUP BY expression"

SELECT * FROM T_TEST WHERE (ISSUEDETAIL = 'DATA1' OR ISSUEDETAIL = 'DATA2' OR ISSUEDETAIL = 'DATA3') GROUP BY TAG ORDER BY TAG, LOCATION
0
Comment
Question by:KingMooBot
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 9

Expert Comment

by:jerrypd
ID: 36474937
you need to list all the fields in T_test in the group by clause...
0
 
LVL 32

Expert Comment

by:awking00
ID: 36474945
I suspect you can have more than one location per tag, which means you somehow have to aggregate the location (e.g. max or min, etc.) in order to group by. Perhaps you can provide some sample data and your expected output to give us a better idea of your requirements.
0
 
LVL 6

Expert Comment

by:markterry
ID: 36474949
When you group by, you need to list the fields that are in the group by in the select statement. You can also list other fields that you want to aggregate with sum(), Count(), Avg() functions (And other aggregate functions).
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36474977
some valid samples:

numbers in order by shows the order of columns...
SELECT TAG, LOCATION
    FROM T_TEST
   WHERE ISSUEDETAIL IN ('DATA1', 'DATA2', 'DATA3')
GROUP BY TAG, LOCATION
ORDER BY TAG, LOCATION

  SELECT TAG, LOCATION, Count(1) TAG_LOCATION_COUNT
    FROM T_TEST
   WHERE ISSUEDETAIL IN ('DATA1', 'DATA2', 'DATA3')
GROUP BY TAG, LOCATION
ORDER BY 1,2,3 desc

  SELECT TAG, Count(1) TAGCOUNT
    FROM T_TEST
   WHERE ISSUEDETAIL IN ('DATA1', 'DATA2', 'DATA3')
GROUP BY TAG
ORDER BY TAG

Open in new window

0
 

Author Comment

by:KingMooBot
ID: 36475043
why would I want to list all columns in the GROUP BY if I only want to group by one column?

enclosed is sample data with 2 results (either result is fine)
 data.xlsx
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36475076
you should understand what group by is doing first...
your sample data is confusing... why you removed line 3,5,7? where is the grouping here?

maybe you confuse group by & order by...

have a look at this page: http://www.w3schools.com/sql/sql_groupby.asp
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 36475304
You had to apply some criteria to get those results. What differentiates the selection of a given location or uid for the same tag?
0
 

Author Comment

by:KingMooBot
ID: 36475345
I usually have 2 identical tags (one from each location). I just want the results to show my unique tags, regardless of how many of each identical tag I have.
0
 
LVL 9

Accepted Solution

by:
jerrypd earned 500 total points
ID: 36475592
that would be a
select distinct tag from t_test
or
select distinct tag, location from t_test
0
 
LVL 32

Expert Comment

by:awking00
ID: 36488639
You show this in your results -
-781709043      4004-10      USA      1      NA543      TEST0      DATA1
-478148541      4006-11      USA      1      NA543      TEST0      DATA1
-478148540      4006-12      USA      2      NA543      TESTX0      DATA1
But this also provides unique tag values -
B8B04AD9291D46F4B  4004-10  CAN      2      VA5      TEST0      DATA1
6ABAB6B13C7918AB6  4006-11  CAN      3      VA5      TESTX0      DATA1
BB00DDFABC27046B2  4006-12  CAN      1      VA5      TESTX0      DATA1
So, the question remains what is the criteria for selecting which record?
0
 

Author Closing Comment

by:KingMooBot
ID: 36489162
thanks. "DISTINCT" is what I was after. Even after reading up on "GROUP BY" again it still sounds like it does the same thing as "distinct"...
0
 

Author Comment

by:KingMooBot
ID: 36489175
Awking: either result would be fine. Just looking for unique tags, regardless of which Location they're from.
0
 
LVL 32

Expert Comment

by:awking00
ID: 36489389
I guess the group by and order by part of the question threw me off :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 92
Extract the first word (before the , ) 2 37
su - oracle could not open session 6 47
selective queries 7 21
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

932 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

12 Experts available now in Live!

Get 1:1 Help Now