how to combine these two queries

Question

I am using the same table and columns for both queries, but I have different aliases and avg function. How would I be able to combine these two queries with UNION ALL because I want every single row that I have selected from them. Obviously I can't do it when I have different select statements. How could I combine these two queries ?

SELECT GRADE_TYPE_CODE, ROUND(TO_CHAR(NUMERIC_GRADE),2) AS GRADE
FROM GRADE
WHERE STUDENT_ID = 10 AND SECTION_ID = 5
ORDER BY GRADE_TYPE_CODE DESC,NUMERIC_GRADE DESC;



SELECT 'Average is:' AS GRADE_TYPE_CODE,AVG(GRADE) AS GRADE
FROM
(
    SELECT AVG(NUMERIC_GRADE) AS GRADE
FROM GRADE
WHERE STUDENT_ID = 10 AND SECTION_ID = 5
GROUP BY GRADE_TYPE_CODE
) myInnerTable;

You have a grade_type_code with a value of 'Average is:' ???? — Tony Hopkinson, Nov 02 '13 at 01:28
Yes , I'd to display it just the way these two queries are. So I'd like to combine them. — Manual, Nov 02 '13 at 01:37

score 3 · Accepted Answer · answered Nov 02 '13 at 01:47

Not sure what the issue is here. Why not just put "union all" between your two queries, and move the ORDER BY clause to the end?

SELECT GRADE_TYPE_CODE, ROUND(TO_CHAR(NUMERIC_GRADE),2) AS GRADE
FROM GRADE
WHERE STUDENT_ID = 10 AND SECTION_ID = 5

union all

SELECT 'Average is:' AS GRADE_TYPE_CODE,AVG(GRADE) AS GRADE
FROM
(
    SELECT AVG(NUMERIC_GRADE) AS GRADE
FROM GRADE
WHERE STUDENT_ID = 10 AND SECTION_ID = 5
GROUP BY GRADE_TYPE_CODE
) myInnerTable

order by 1 desc, 2 desc

http://sqlfiddle.com/#!4/99a16/19/0

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

I'm thinking you could create a temporary table and then union ALL from the two separate queries.

edited May 23 '17 at 12:33

Community

1
1

answered Nov 02 '13 at 01:26

Walter H.

61
4

how to combine these two queries

2 Answers2