Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Help please?

Posted on 2011-04-21
12
Medium Priority
?
171 Views
Last Modified: 2012-08-13
Hi Experts,

I have this query (attached)

And it returns all records as it should do.

However there are some records that are £0 and would not like these to be shown.

This is where i would like your help

Im guessing it would a "where" totalSpend <0

or something like that??

Many thanks for your time.


$query = "SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), c.renewal_month, SUM(Jan12), SUM(Feb12), SUM(Mar12), SUM(Apr12), SUM(May12), SUM(Jun12), SUM(Jul12), SUM(Aug12), SUM(Sep12), SUM(Oct12), SUM(Nov12), SUM(Dec12), 
 SUM(CASE renewal_month WHEN 1 THEN (Jan12 + Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12)
WHEN 2 THEN (Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 3 THEN (Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 4 THEN (Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 5 THEN (May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 6 THEN (Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 7 THEN (Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 8 THEN (Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 9 THEN (Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 10 THEN (Oct12 + Nov12 + Dec12) 
WHEN 11 THEN (Nov12 + Dec12) 
WHEN 12 THEN (Dec12) END)

 AS totalSpend FROM customer c 
LEFT JOIN bill_detail2012 d
  ON c.customer_name = d.customer_name 
WHERE c.network = '" . $O2 . "'
GROUP BY customer_name
ORDER BY  SUM(Jan12)+ SUM(Feb12)+SUM(Mar12)+SUM(Apr12)+SUM(May12)+SUM(Jun12)+SUM(Jul12)+ SUM(Aug12)+ SUM(Sep12)+ SUM(Oct12) +SUM(Nov12) +SUM(Dec12) DESC";

Open in new window

0
Comment
Question by:NeoAshura
  • 6
  • 4
  • 2
12 Comments
 
LVL 21

Expert Comment

by:K V
ID: 35439297
...... GROUP BY customer_name
 HAVING totalSpend>0 .....
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35439302
try this
$query = "

Select X.customer_name, X.network, X.kit_fund_level, X.COUNT(mobile_number), X.renewal_month, X.Jan12, X.Feb12, X.Mar12, X.Apr12, X.May12, X.Jun12, X.Jul12, X.Aug12, X.Sep12,X.Oct12,X.Nov12,X.Dec12, X.totalspend
From (
SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), c.renewal_month, SUM(Jan12) as Jan12, SUM(Feb12) as Feb12, SUM(Mar12) as Mar12, SUM(Apr12) as Apr12, SUM(May12) as May12, SUM(Jun12) as Jun12, SUM(Jul12) as Jul12, SUM(Aug12) as Jul12, SUM(Sep12) as Sep12, SUM(Oct12) as Oct12, SUM(Nov12) as Nov12, SUM(Dec12) as Dec12, 
 SUM(CASE renewal_month WHEN 1 THEN (Jan12 + Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12)
WHEN 2 THEN (Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 3 THEN (Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 4 THEN (Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 5 THEN (May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 6 THEN (Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 7 THEN (Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 8 THEN (Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 9 THEN (Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 10 THEN (Oct12 + Nov12 + Dec12) 
WHEN 11 THEN (Nov12 + Dec12) 
WHEN 12 THEN (Dec12) END)

 AS totalSpend FROM customer c 
LEFT JOIN bill_detail2012 d
  ON c.customer_name = d.customer_name 
WHERE c.network = '" . $O2 . "'
GROUP BY customer_name ) X
Where X.totalspend > 0
ORDER BY  (X.Jan12+ X.Feb12+X.Mar12+X.Apr12+X.May12+X.Jun12+X.Jul12+ X.Aug12+ X.Sep12+ X.Oct12 +X.Nov12 +X.Dec12) DESC";

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35439311
or try this
$query = "SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), c.renewal_month, SUM(Jan12), SUM(Feb12), SUM(Mar12), SUM(Apr12), SUM(May12), SUM(Jun12), SUM(Jul12), SUM(Aug12), SUM(Sep12), SUM(Oct12), SUM(Nov12), SUM(Dec12), 
 SUM(CASE renewal_month WHEN 1 THEN (Jan12 + Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12)
WHEN 2 THEN (Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 3 THEN (Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 4 THEN (Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 5 THEN (May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 6 THEN (Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 7 THEN (Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 8 THEN (Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 9 THEN (Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 10 THEN (Oct12 + Nov12 + Dec12) 
WHEN 11 THEN (Nov12 + Dec12) 
WHEN 12 THEN (Dec12) END)

 AS totalSpend FROM customer c 
LEFT JOIN bill_detail2012 d
  ON c.customer_name = d.customer_name 
WHERE c.network = '" . $O2 . "'
GROUP BY customer_name
Having 
SUM(CASE renewal_month WHEN 1 THEN (Jan12 + Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12)
WHEN 2 THEN (Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 3 THEN (Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 4 THEN (Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 5 THEN (May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 6 THEN (Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 7 THEN (Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 8 THEN (Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 9 THEN (Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 10 THEN (Oct12 + Nov12 + Dec12) 
WHEN 11 THEN (Nov12 + Dec12) 
WHEN 12 THEN (Dec12) END) >0
ORDER BY  SUM(Jan12)+ SUM(Feb12)+SUM(Mar12)+SUM(Apr12)+SUM(May12)+SUM(Jun12)+SUM(Jul12)+ SUM(Aug12)+ SUM(Sep12)+ SUM(Oct12) +SUM(Nov12) +SUM(Dec12) DESC";

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Author Comment

by:NeoAshura
ID: 35439338
I tried both of them and neither worked,

@Ghost: Your query removed the actually customers and just should the "totals" of them all added together

and

@pratima: Your query said my tables where corrupt.

Thanks for the attempt guys its appreciated.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35439348
have you tried both?
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35439351
again pratima, that query does the same as ghosts, removes all the "customers" and just leaves the total at the bottom. Each customer has a total if the total of that customer is 0 i dont want them to be displayed.

0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35439357
ffs hang on might of had a blonde moment
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35439366
Nope still does the same thing im afraid :(
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35439385
also when i do (attached)

in mysql it also returns 0 sets.

even tho i know for a fact customers in that table have "totals"
SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), c.renewal_month, SUM(Jan09), SUM(Feb09), SUM(Mar09), SUM(Apr09), SUM(May09), SUM(Jun09), SUM(Jul09), SUM(Aug09), SUM(Sep09), SUM(Oct09), SUM(Nov09), SUM(Dec09), 
 SUM(CASE renewal_month WHEN 1 THEN (Jan09 + Feb09 + Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09)
WHEN 2 THEN (Feb09 + Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 3 THEN (Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 4 THEN (Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 5 THEN (May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 6 THEN (Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 7 THEN (Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 8 THEN (Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 9 THEN (Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 10 THEN (Oct09 + Nov09 + Dec09) 
WHEN 11 THEN (Nov09 + Dec09) 
WHEN 09 THEN (Dec09) END)

 AS totalSpend FROM customer c 
LEFT JOIN bill_detail2009 d
  ON c.customer_name = d.customer_name 
WHERE c.network = 'O2'
GROUP BY customer_name
Having 
SUM(CASE renewal_month WHEN 1 THEN (Jan09 + Feb09 + Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09)
WHEN 2 THEN (Feb09 + Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 3 THEN (Mar09 + Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 4 THEN (Apr09 + May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 5 THEN (May09 + Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 6 THEN (Jun09 + Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 7 THEN (Jul09 + Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 8 THEN (Aug09 + Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 9 THEN (Sep09 + Oct09 + Nov09 + Dec09) 
WHEN 10 THEN (Oct09 + Nov09 + Dec09) 
WHEN 11 THEN (Nov09 + Dec09) 
WHEN 09 THEN (Dec09) END) >0
ORDER BY  SUM(Jan09)+ SUM(Feb09)+SUM(Mar09)+SUM(Apr09)+SUM(May09)+SUM(Jun09)+SUM(Jul09)+ SUM(Aug09)+ SUM(Sep09)+ SUM(Oct09) +SUM(Nov09) +SUM(Dec09) DESC

Open in new window

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 total points
ID: 35439449
you need to do like this only

try on query analyser and check whether it fails
$query = "

Select X.customer_name, X.network, X.kit_fund_level, X.COUNT(mobile_number), X.renewal_month, X.Jan12, X.Feb12, X.Mar12, X.Apr12, X.May12, X.Jun12, X.Jul12, X.Aug12, X.Sep12,X.Oct12,X.Nov12,X.Dec12, X.totalspend
From (
SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), c.renewal_month, SUM(Jan12) as Jan12, SUM(Feb12) as Feb12, SUM(Mar12) as Mar12, SUM(Apr12) as Apr12, SUM(May12) as May12, SUM(Jun12) as Jun12, SUM(Jul12) as Jul12, SUM(Aug12) as Jul12, SUM(Sep12) as Sep12, SUM(Oct12) as Oct12, SUM(Nov12) as Nov12, SUM(Dec12) as Dec12, 
 SUM(CASE renewal_month WHEN 1 THEN (Jan12 + Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12)
WHEN 2 THEN (Feb12 + Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 3 THEN (Mar12 + Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 4 THEN (Apr12 + May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 5 THEN (May12 + Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 6 THEN (Jun12 + Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 7 THEN (Jul12 + Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 8 THEN (Aug12 + Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 9 THEN (Sep12 + Oct12 + Nov12 + Dec12) 
WHEN 10 THEN (Oct12 + Nov12 + Dec12) 
WHEN 11 THEN (Nov12 + Dec12) 
WHEN 12 THEN (Dec12) END)

 AS totalSpend FROM customer c 
LEFT JOIN bill_detail2012 d
  ON c.customer_name = d.customer_name 
WHERE c.network = '" . $O2 . "'
GROUP BY customer_name ) X
Where X.totalspend > 0
ORDER BY  (X.Jan12+ X.Feb12+X.Mar12+X.Apr12+X.May12+X.Jun12+X.Jul12+ X.Aug12+ X.Sep12+ X.Oct12 +X.Nov12 +X.Dec12) DESC";

Open in new window

0
 
LVL 21

Assisted Solution

by:K V
K V earned 1000 total points
ID: 35439525
I suppose that's what you wanted to do!!  make sure you've totals!!

" group by customer_name HAVING totalspend>0 " will filter out all the records having totalspent<0 !!

You try with HAVING totalspend>=0 and you should get customers with 0s back.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35439622
ahh got ya, I fixed it now. was doing something stupidly wrong/

thanks for your time.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month21 days, 4 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question