CODEWITHSUNDEEP
sqloracle-databasecartesian-product
K Green
K Green

Reputation: 129

Oracle SQL Developer - Combining Cartesian Product, Count/Sum and Group By

I am joining 2 tables using Cartesian Product as follows. 

select p.type, i.amount 
FROM products p, invoice i
where (p.account = i.id); -- column headers cant be changed despite having same info

This of course displays 2 columns with the respective values.

However I then want to manipulate the data further using COUNT/GROUP BY and SUM/GROUP BY but have been unable to find any relevant examples which I could get to work using 2 tables. (doing them all separately is ok, just getting them to work together is the problem).

For the end result, I want to show 3 columns showing a grouping of the 4 types, with the count of the entries falling under each type, and the total amount/SUM for each type, eg.

Type - Count of Types - Total Value

A - 5 - 500

B - 6 - 1000

C - 1 - 50

D - 2 - 100

Upvotes: 0

Views: 1554

Answers (2)

David Aldridge
David Aldridge

Reputation: 52376

Bear in mind that the result of a query is logically equivalent to a table or a view -- all of them are a projection of zero or more rows with one or more columns.

In general what you can do to a table in terms of select you can also do to a query or a view. Despite some limitations you can also apply deletes and updates to views and queries just as you can to a table.

If in doubt, create the query that returns the data you want, and then treat it as a table:

select   type,
         count(type) count_type,
         sum(amount) sum(amount)
from     (select ... )
group by type

Note also the subquery factoring clause, which has particular appplications if you need to reference the same logical data set multiple times:

with my_data as(
    select ... )
select   type,
         count(type) count_type,
         sum(amount) sum(amount)
from     my_data
where    type in (select ... from my_data)
group by type

Upvotes: 0

rs.
rs.

Reputation: 27467

Did you try this?

select p.type, count(p.type) Cnt, sum(i.amoumt) Total
FROM products p, invoice i
where (p.account = i.id)
group by p.type

Upvotes: 2

Related Questions