Query Help please?

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

LVL 6
NeoAshuraAsked:
Who is Participating?
 
Pratima PharandeCommented:
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
 
theGhost_k8Database ConsultantCommented:
...... GROUP BY customer_name
 HAVING totalSpend>0 .....
0
 
Pratima PharandeCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Pratima PharandeCommented:
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
 
NeoAshuraAuthor Commented:
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
 
Pratima PharandeCommented:
have you tried both?
0
 
NeoAshuraAuthor Commented:
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
 
NeoAshuraAuthor Commented:
ffs hang on might of had a blonde moment
0
 
NeoAshuraAuthor Commented:
Nope still does the same thing im afraid :(
0
 
NeoAshuraAuthor Commented:
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
 
theGhost_k8Database ConsultantCommented:
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
 
NeoAshuraAuthor Commented:
ahh got ya, I fixed it now. was doing something stupidly wrong/

thanks for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.