aspose file tools*
The moose likes JDBC and the fly likes Why this normal customer group never show any figures? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Why this normal customer group never show any figures?" Watch "Why this normal customer group never show any figures?" New topic
Author

Why this normal customer group never show any figures?

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 598
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

Joined: Mar 17, 2012
Posts: 892
    
    9
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Aug 02, 2012
Posts: 598
Yes, I solved it.

Thanks Martin, I learned a lot from you
Jack


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why this normal customer group never show any figures?