CODEWITHSUNDEEP
sqloracle11g
olovholm
olovholm

Reputation: 1392

Creating alias based on result of where clause

I'm creating a view where I pretty much replicate the data of the original user, but I also have an attribute in which I want to put the number of occurrences the user has in the downloads table. 

CREATE VIEW customer_count_streams_vw(
sid_customer
systemuser_id
postcode
age
gender
num_streams) 
AS 
SELECT
user.sid_customer,
user.systemuser_id,
user.postcode,
user.age,
user.gender,
num_streams 
FROM md_customer 
INNER JOIN ods_streaming AS num_streams (
SELECT COUNT(ods_streaming.sid_customer) 
WHERE ods_streaming.sid_customer = md_customer.sid_customer)

What I want is to place the result of the part: 

SELECT COUNT(ods_streaming.sid_customer) 
WHERE ods_streaming.sid_customer = md_customer.sid_customer

into the num_streams field.

Upvotes: 1

Views: 104

Answers (3)

rs.
rs.

Reputation: 27427

Your query should be

SELECT
user.sid_customer,
user.systemuser_id,
user.postcode,
user.age,
user.gender,
num_streams 
FROM md_customer 
INNER JOIN 
( 
        SELECT sid_customer, COUNT(ods_streaming.sid_customer) num_streams 
        FROM ods_streaming group by sid_customer
) AS ods_streaming  
ON ods_streaming.sid_customer = md_customer.sid_customer

Above query will return rows for customers with row in md_customer and also a row in ods_streaming. If you want all customers and their count (including 0) then your query should be

SELECT
cust.sid_customer,
cust.systemuser_id,
cust.postcode,
cust.age,
cust.gender,
COUNT(strm.sid_customer) num_streams 
FROM 
   md_customer cust
LEFT OUTER JOIN ods_streaming  strm   
   ON cust.sid_customer = strm.sid_customer
group by 
cust.sid_customer,
cust.systemuser_id,
cust.postcode,
cust.age,
cust.gender

Upvotes: 1

DVPassos
DVPassos

Reputation: 21

Maybe you can try to use the group by for the count, instead of a sub-select.

SELECT
md_customer.sid_customer,
md_customer.systemuser_id,
md_customer.postcode,
md_customer.age,
md_customer.gender,
count(ods_streaming.num_streams)
FROM md_customer 
INNER JOIN ods_streaming 
on ods_streaming.sid_customer = md_customer.sid_customer
group by 1,2,3,4,5;

You should avoid doing sub-selects like these... this group by should make things a little bit faster

Upvotes: 0

Tulains Córdova
Tulains Córdova

Reputation: 2639

SELECT
    u.sid_customer,
    u.systemuser_id,
    u.postcode,
    u.age,
    u.gender,
    num_streams.amount
FROM 
    md_customer u INNER JOIN (
            SELECT 
                ods_streaming.sid_customer,
                COUNT(ods_streaming.sid_customer) as amount 
            FROM
                ods_streaming 
            GROUP BY ods_streaming.sid_customer

        ) num_streams  ON ( num_streams.sid_customer = u.sid_customer )

Besides: user is a reserved word in most, if not all, database engines

Upvotes: 0

Related Questions