Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why this normal customer group never show any figures?

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Any help would be greatly appreciated!
Thanks

 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Remove all the unnecessary stuff from the query and reduce it to the most basic query that shows the problem. If you do that you might even be able to find the problem yourself.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I solved it.

Thanks Martin, I learned a lot from you
Jack


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic