• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Why this normal customer group never show any figures?

 
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, I solved it.

Thanks Martin, I learned a lot from you
Jack


 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic