CODEWITHSUNDEEP
sqloracle-database
Stunna
Stunna

Reputation: 413

Oracle: Aggregated Counts on Tables

I'm encountering difficulties trying to get an overall count, as well as counts on each status into a flat table.

To help understand the table structures, here is an example of the 2 tables I am working with.

Heroes:

Pin             Name                 Status
1               Batman               Fighting Crime
1               Superman             Fighting Crime
1               Daredevil            Eating Food
2               Spiderman            Watching TV         
3               Wolverine            Eating Food
4               Cyclops              Watching TV
4               Jean Gray            Watching TV
4               Prof X               Eating Food

And then the other table, Locations:

Loc_Pin         Place     
1               Gotham City
2               Cloud City
3               New York City
4               Washington DC
5               My House

Using these 2 tables, I'm hoping to get an expected output that would look like:

LOCATION          Number of Heroes       Number Fighting         Number Eating          Number Watching TV
Gotham City       3                      2                       1                      0
Cloud City        1                      0                       0                      1
New York City     1                      0                       1                      0
Washington DC     3                      0                       1                      2
My House          0                      0                       0                      0

I feel like I'm almost there. I first have my select statement lined up to get an aggregate count of status and heroes to their respective locations. What my major issue is I need to represent 0 in the tables instead of nulls. So on my update statement, I'm trying to point to itself, which I believe is my problem I am running into.

Here is an example of my SQL query that I am so far. I am only trying to populate "fighting crime" and do a little proof of concept right now and figure I can roll into consideration the other statuses by expanding the case statements:

MERGE
INTO Counts finalCount
USING (
    SELECT DISTINCT
        myCounts.PIN
        ,myCounts.status
        ,holder.status
        ,holder.status_count
    FROM
        Counts myCounts
        , (select   count(heroes.status) status_count,
                            heroes.status status,
                            loc.loc_pin loc_pin

                    from     heroes_permit_mv heroes,
                            Location loc

                    where    heroes.pin = loc.pin
                    group by heroes.status, loc.loc_pin) holder
    WHERE   myCounts.pin = holder.pin
) updateMe
ON (finalCount.PIN = updateMe.PIN)
WHEN MATCHED THEN
UPDATE
    SET finalCount.status = CASE WHEN TRIM(updateMe.status) = 'Fighting Crime' THEN updateMe.status_count ELSE 0 END

But I'm running into [Error] Execution (2: 6): ORA-30926: unable to get a stable set of rows in the source tables

I've tried to look up this error and the most prevalent answer was to add a DISTINCT statement into my SQL query, which I did and still doesn't seem to help. When I pick my query apart piece by piece outside of the update statement, it all resolves and behaves as expected. It's just when I do my final update statement, I get this error from Oracle.

I've also tried to attack this using an UPDATE statement, but I get another error saying [Error] Execution (21: 8): ORA-01779: cannot modify a column which maps to a non key-preserved table.

Any tips at this point would be greatly appreciated as I've been racking my head against this for the past couple of days.

Upvotes: 1

Views: 62

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522151

You can use a pivot query with conditional aggregation:

SELECT t1.Place AS LOCATION,
       COUNT(*) AS Number_Of_Heroes,
       SUM(CASE WHEN COALESCE(t2.Status, '') = 'Fighting Crime'
                THEN 1 ELSE 0 END) AS Number_Fighting,
       SUM(CASE WHEN COALESCE(t2.Status, '') = 'Eating Food' 
                THEN 1 ELSE 0 END) AS Number_Eating,
       SUM(CASE WHEN COALESCE(t2.Status,'') = 'Watching TV' 
                THEN 1 ELSE 0 END) AS Number_Watching_TV
FROM Locations t1 INNER JOIN Heroes t2
    ON t1.Loc_Pin = t2.Pin
GROUP BY t1.Place

Upvotes: 2

user5683823
user5683823

Reputation:

The pivot query using the Oracle 11 pivot syntax:

select * from (
    select pin, count(*) over (partition by pin) Number_of_Heroes, name, status
    from heroes
              )
pivot (count(name) for status in ('Fighting Crime', 'Eating Food', 'Watching TV'));

You can outer-join the result with the Locations table to get the final result.

Output:

       PIN NUMBER_OF_HEROES 'Fighting Crime' 'Eating Food' 'Watching TV'
---------- ---------------- ---------------- ------------- -------------
         2                1                0             0             1
         1                3                2             1             0
         3                1                0             1             0
         4                3                0             1             2

Upvotes: 1

Related Questions