CODEWITHSUNDEEP
oracle-database
katfysh
katfysh

Reputation: 15

Trying to take count results and give them their own column

I am making my first post here since I have not been able to find a solution on my own. I have little programming/sql/oracle knowledge, but I occasionally work with (I believe) Oracle databases.

What I am trying to accomplish is taking a query result and giving each COUNT() its own column. the reason for this is to make a report for a web application. I need the results to fill a column of their own in order to parse out fields for the report designer.

My query looks like the following:

SELECT ISSUE, COUNT(ISSUE) AS ISSUE_COUNT FROM ISSUE_TABLE WHERE TIME >TO_DATE('08/10/2017 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TIME <TO_DATE('08/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS') GROUP BY ISSUE

This would return a result as follows:

ISSUE      | ISSUE_COUNT
------------------------
A          | 30
B          | 4
C          | 24
D          | 17

What I'd like to end up with is something like this:

ISSUE        | A  | B  | C  | D  |
----------------------------------
ISSUE_COUNT  | 30 | 4  | 24 | 17 |

I need to ensure that each ISSUE_COUNT is unique in its own column or the report application will not parse out the results to display when reports are run. I thought about using PIVOT, but have not found any solution that uses COUNT() or SUM().

Upvotes: 0

Views: 43

Answers (1)

MagdielAybar
MagdielAybar

Reputation: 187

You need to use the PIVOT clause. Try this query:

SELECT * FROM (SELECT ISSUE, COUNT(ISSUE) AS ISSUE_COUNT FROM ISSUE_TABLE WHERE TIME >TO_DATE('08/10/2017 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TIME <TO_DATE('08/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS') GROUP BY ISSUE )PIVOT (SUM(ISSUE_COUNT) FOR ISSUE IN ('A','B','C','D'))

Upvotes: 0

Related Questions