CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregate-functions
user147219
user147219

Reputation: 375

How to add up summed values from a column where SELECT used two tables in SQL in Oracle

I am working on an assignment where I need to add up some values according to their common codes (i.e. if one table has values like

  code   |   name
---------------------
1        |   blah
2        |   blah1
3        |   blah2
1        |   blah

and then table 2 has

code | amount
-------------
1    | 100
2    | 200
3    | 250
1    | 125

I need to add up all the values with a common code, so like add up the amounts for both values with a code of 1, the amounts for values with a code of 2, and the amounts for values with a code of 3)

but then I also need to total up all the results. I have gotten so far as adding up rows with common codes. I'm just having trouble adding up the totals, or perhaps just add up all the values from the 'amounts' column. I would like to have it in a single statement, though, so I was thinking it would be easier to add up the separate totals of the amounts.

I think I have the idea down, I just don't know how to actually execute it.

Right now, I have this, I've tried several variations, including using a HAVING clause but still just got errors:

SELECT X_STORE.STORE_NAME AS "store name", SUM(X_STORE_SALES.WEEKLY_SALES) AS "total weekly sales", SUM(SUM(X_STORE_SALES.WEEKLY_SALES)) AS "grand total"
FROM X_STORE JOIN X_STORE_SALES ON X_STORE.STORE_CODE = X_STORE_SALES.STORE_CODE
GROUP BY STORE_NAME;

and here are the codes for the tables for reference purposes:

CREATE TABLE X_STORE (
  STORE_CODE integer,
  STORE_NAME varchar2(20)
);

INSERT INTO X_STORE VALUES('1','Access Junction');
INSERT INTO X_STORE VALUES('2','Database Corner');
INSERT INTO X_STORE VALUES('3','Tuple Charge');
INSERT INTO X_STORE VALUES('4','Attribute Alley');
INSERT INTO X_STORE VALUES('5','Primary Key Point');

CREATE TABLE X_STORE_SALES (
  ROW_NUMBER INT,
  STORE_CODE INT,
  WEEKLY_SALES DECIMAL(12,2),
  REGION_CODE INT
);

INSERT INTO X_STORE_SALES VALUES (1, 1, 854558.38, 1);
INSERT INTO X_STORE_SALES VALUES (2, 2, 689475.89, 1);
INSERT INTO X_STORE_SALES VALUES (3, 3, 978046.91, 1);
INSERT INTO X_STORE_SALES VALUES (4, 4, 826270.78, 1);
INSERT INTO X_STORE_SALES VALUES (5, 5, 314859.95, 1);
INSERT INTO X_STORE_SALES VALUES (6, 1, 402342.82, 2);
INSERT INTO X_STORE_SALES VALUES (7, 2, 326841.11, 2);
INSERT INTO X_STORE_SALES VALUES (8, 3, 503921.45, 2);
INSERT INTO X_STORE_SALES VALUES (9, 4, 949408.97, 2);
INSERT INTO X_STORE_SALES VALUES (10, 5, 581574.05, 2);
INSERT INTO X_STORE_SALES VALUES (11, 1, 638178.3, 3);
INSERT INTO X_STORE_SALES VALUES (12, 2, 511723.79, 3);
INSERT INTO X_STORE_SALES VALUES (13, 3, 556421.29, 3);
INSERT INTO X_STORE_SALES VALUES (14, 4, 951686.38, 3);
INSERT INTO X_STORE_SALES VALUES (15, 5, 902503.64, 3);
INSERT INTO X_STORE_SALES VALUES (16, 1, 443410.39, 4);
INSERT INTO X_STORE_SALES VALUES (17, 2, 281833.15, 4);
INSERT INTO X_STORE_SALES VALUES (18, 3, 409222.16, 4);
INSERT INTO X_STORE_SALES VALUES (19, 4, 853611.42, 4);
INSERT INTO X_STORE_SALES VALUES (20, 5, 118228.64, 4);

The expected output would be something like:

store name        | total weekly sales
--------------------------------------
Attribute Alley   |         3580977.55
--------------------------------------
Primary Key Point |         1917166.28
--------------------------------------
Database Corner   |         1809873.94
--------------------------------------
Access Junction   |         2338489.89
--------------------------------------
Tuple Charge      |         2447611.81
--------------------------------------
grand total (or   |
just null, doesn't|      12,094,119.47
really matter)    |

but instead, with the last idea that I attempted, I am getting an error message that only says: ORA-00923: not a single-group group function

One thing I think I may be doing wrong is querying the information as if it would show up in a new column, and so it's breaking because of that, but I don't know how to fix the issue and I don't know what to 'google' to get an idea.

Upvotes: 1

Views: 50

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you just want a row with totals, use grouping sets:

select s.store_name,
       sum(ss.weekly_sales) as total_weekly_sales
from x_store s inner join
     x_store_sales ss
     on ss.store_code = s.store_code
group by grouping sets ( (s.store_code, s.store_name), () );

Upvotes: 2

forpas
forpas

Reputation: 164069

You will join the tables and use group by to get the grouped sums.
Then with UNION you will add to the results the total: 

select 
  t.store_name,
  sum(s.weekly_sales) total_weekly_sales
from x_store t inner join x_store_sales s
on s.store_code = t.store_code
group by t.store_code, t.store_name
union all
select 
  'grand total',
  sum(weekly_sales) total_weekly_sales
from x_store_sales

See the demo.
Or more efficiently with a CTE:

with cte as (
  select 
    t.store_name,
    sum(s.weekly_sales) total_weekly_sales
  from x_store t inner join x_store_sales s
  on s.store_code = t.store_code
  group by t.store_code, t.store_name
)
select * from cte
union all
select 
  'grand total',
  sum(total_weekly_sales)
from cte

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

Remove the grand total. Besides, you had a small bug in your query: you wrote SUM(SUM(...)). That's not valid.

If you remove the grand total it should work, as in:

SELECT
  X_STORE.STORE_NAME AS "store name", 
  SUM(X_STORE_SALES.WEEKLY_SALES) AS "total weekly sales"
FROM X_STORE
JOIN X_STORE_SALES ON X_STORE.STORE_CODE = X_STORE_SALES.STORE_CODE
GROUP BY STORE_NAME

Result:

store name         total weekly sales 
-----------------  ------------------ 
Primary Key Point  1,917,166.28       
Access Junction    2,338,489.89       
Attribute Alley    3,580,977.55       
Tuple Charge       2,447,611.81       
Database Corner    1,809,873.94

Upvotes: 2

Related Questions