In this code snippet, although the VIP Level Up param is $6,000
and there is a customer who only made a sales of just $1,000
and the total is $30,000 which was correct as a sum of both
the correct figures should be
VIP - $29,000
Normal - $1,000
Level Up Parameter - $6,000
Why the figures turn out to be
VIP - $30,000
Normal - $0?
It would help tremendously if you posted the SQL query itself, formatted to be readable. This string declaration is quite hard to follow.
Anyway, you use GROUP BY v.LevelUpAmount in your subqueries. I assume there is only one record in the VIPLevelUpParam table. So you're effectively grouping all records (customers) together. I'd suggest a tutorial or documentation to get firmer understanding of what GROUP BY does.
What you need to do is to group by customer first, to obtain a sum of sales (perhaps purchases would be a better term?) per customer. Then you'd assign a customer his status (VIP or Normal according to his purchases), and then group it by the customer status.
SQL statements can usually be build up layer by layer. In this case, construct the query to compute sum of purchases per customer, then use it as an inner query to assign status of customer, and then use this compound query once again as an inner query to compute sum of purchases in each status group. A WITH command might be pretty useful here, if your database supports it.
Joined: Aug 02, 2012
Yes, I solved it.
Thanks Martin, I learned a lot from you
subject: Why this normal customer group never show any figures?