Group By

Can you select any column in a Group by query even if that column is not groupable?
e.g. Select Dept, Empno, Ave(salary) From T1 Group by Dept
Can we select empno in the above query?

Questions by madhurap   answers by madhurap

Showing Answers 1 - 5 of 5 Answers

Vaishali Sisodia

  • Dec 5th, 2008
 

Group By nothing but it is a summarization(grouped) of rows on tha basis of column or columns. It apply some sort of aggregate function to certain columns like
SELECT name, sum(salary)
FROM emp Where
GROUP BY name;
* In this SELECT statement name column doesnt have aggregate function so it should be GROUP BY.

Having Clause is used only with GROUP BY clause for filtration purpose. When u want to apply some condition with in SELECT statement which use GROUP BY clause, use HAVING clause to apply condition. like
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
*In this SELECT statement HAVING clause is used to filter the records that a GROUP BY returns.

Order BY clause is used to sort the records in Ascending or Descending Order. By default it sort the records in Ascending Order. ORDER BY clause can only be used in SELECT statements.
(without GROUP BY) like
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;

(With Group By) Like
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000
ORDER BY department;

SELECT name, dept, SUM(salary)
FROM emp
GROUP BY name;
* This SELECT statement give ERROR (in SPUFI, It gives -112 Error Code). because GROUP BY clause didnt apply to dept column.
* if any column have aggregate function(salary) in SELECT Statement, u should apply GROUP BY Clause to other columns which dont have Aggregate function(name, dept).

Correct SELECT Statement :  

SELECT name, dept, SUM(salary)
FROM emp
GROUP BY name, dept;

Plz Confirm my Ans is correct or not.

serken

  • Dec 19th, 2008
 

Instead of typing about 100 lines in previous answer, the actual answer to the original question is:

NO, EMPNO CANNOT BE SELECTED IN THIS WAY.

Period.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions