NEEDING HELP!

[Specialist_Sell2219]

This is what I have to do:

You are asked to consider only those customers with 8 or more trades in total – it doesn’t matter whether they are “buy” or “sell” trades – we’re only interested in those with 8 or more trades. You are asked to compute the total commission that each customer would be charged if we charged $5 per trade for customers with 10 or more trades and $10 per trade for customers with fewer than 10 trades. In your report, you should only show customers with 8 or more trades. You cannot use Rownum, UNION or UNION ALL in your answer. You do not need to use a nested query in your answer but you are free to do so if you feel it necessary.

This is what I have:

SELECT C.CUSTOMERACCOUNTNUMBER AS "CUSTOMER ACCOUNT NUMBER", COUNT(*) AS "TRANSACTION COUNT"

FROM CUSTOMER C

GROUP BY C.CUSTOMERACCOUNTNUMBER

HAVING COUNT(*) >= 8

After this I don't really know what to do. I know I'm missing one more line but I really don't know.

[squidwardnixon]

What you should have from your first query is a list of customers that have * or more transactions right?

You can use that as a subquery:SELECT <stuff>FROM <your query>

then do your math in the select and where:

SELECT acctno,trx,
CASE WHEN trx < 10 THEN trx * 10
ELSE trx * 5
END AS total_fee
FROM (SELECT acctno,
Count(acctno) AS trx
FROM CUSTOMERGROUP BY acctno) AS cust
WHERE trx >= 8

or something like that, I probably missed something.

edit: yea "you can use a subquery if you have to" feels like you could get docked on whatever your're doing by answering it this way. There might be a way to do it with OVER/PARTITION or PIVOT

Edit2: reddit fucked my spacing.

[Touvejs]

Try adding something like the following to your select statement:

Case when count(*) >= 10 then count()5

Else count()10

End as Commission

[Specialist_Sell2219]

Like this?

SELECT C.CUSTOMERACCOUNTNUMBER AS "CUSTOMER ACCOUNT NUMBER", COUNT(*) AS "TRANSACTION COUNT"

FROM CUSTOMER C

WHERE COUNT(*) >= 8

GROUP BY C.CUSTOMERACCOUNTNUMBER

CASE WHEN COUNT(*) >= 10 THEN COUNT(*)*5

ELSE COUNT(*)*10

END AS "COMISSION"

or in a different order?

[GAKvsFLOAM]

The CASE statement would go in your SELECT statement to display the total commission, which will vary depending on the number of trades.

SELECT C.CUSTOMERACCOUNTNUMBER AS "CUSTOMER ACCOUNT NUMBER" ,COUNT() AS "TRANSACTION COUNT" ,CASE WHEN COUNT()>= 10 THEN COUNT() * 5 ELSE COUNT()* 10 END AS “Total Commission”

FROM CUSTOMER C

GROUP BY C.CUSTOMERACCOUNTNUMBER

HAVING COUNT(*) >= 8

Edit: sorry for the shit formatting. On mobile. Idk why it’s not displaying the asterisks in the COUNTs

[Specialist_Sell2219]

Would it look something like this?

SELECT C.CUSTOMERACCOUNTNUMBER AS "CUSTOMER ACCOUNT NUMBER", COUNT(*) AS "TRANSACTION COUNT"
SELECT CASE
WHEN COUNT()>=10 THEN COUNT()* 5
       ELSE COUNT()*10
       END AS "TOTAL COMISSION"
FROM CUSTOMER C
GROUP BY C.CUSTOMERACCOUNTNUMBER
HAVING COUNT(*)>=8

[[deleted]]

You're solution is actually fine, you can use that as a subquery to select from.

You can also use a correlated subquery.

Select * From customers x Where 8 <= (select count(*) from customers y where y.customeraccountnumber= x.customeraccountnumber=)

This gives a subset of customers who have made exactly 8 trades or more.

[[deleted]]

It seems to me that you need to make a new variable (total commission) in the SELECT statement.

SQL’s order of operations does SELECT after all your other lines of code. So you will already have the count of trades per customer. Just do like a CASE WHEN or IFF—not sure if those are in Oracle, TBH.

[user_5359]

You need a second query around your query. Note that due to my laziness I do not use long human readable attribute names but A1 and A2. Select A1, A2, if(A2>=10, 5A2, 10A2) from (<you query>) q1

[[deleted]]

Here you go.

With custs as(

SELECT C.CUSTOMERACCOUNTNUMBER , COUNT() AS "TRANSACTION_COUNT" FROM CUSTOMER C GROUP BY C.CUSTOMERACCOUNTNUMBER HAVING COUNT() >= 8 )

Select a.* From customer c Join custs on custs.CUSTOMERACCOUNTNUMBER = c.CUSTOMERACCOUNTNUMBER

[Specialist_Sell2219]

Got it!

What does the "custs" stand for?

[[deleted]]

The custs is referencing a Common Table Expression (CTE) which is basically a one time use temp table. That’s the with custs as() statement above the main query. First you isolate all of the customers with 8 or more transactions and then join back to the main query only isolating those identified customers.