KingMooBot
asked on
Can't seem to GROUP BY and ORDER BY in sql query
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
SELECT * FROM T_TEST WHERE (ISSUEDETAIL = 'DATA1' OR ISSUEDETAIL = 'DATA2' OR ISSUEDETAIL = 'DATA3') GROUP BY TAG ORDER BY TAG, LOCATION
you need to list all the fields in T_test in the group by clause...
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.
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).
some valid samples:
numbers in order by shows the order of columns...
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
ASKER
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
enclosed is sample data with 2 results (either result is fine)
data.xlsx
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
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
You had to apply some criteria to get those results. What differentiates the selection of a given location or uid for the same tag?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
-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?
ASKER
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"...
ASKER
Awking: either result would be fine. Just looking for unique tags, regardless of which Location they're from.
I guess the group by and order by part of the question threw me off :-)