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.
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";
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";
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";ASKER
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.
@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.
have you tried both?
ASKER
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.
ASKER
ffs hang on might of had a blonde moment
ASKER
Nope still does the same thing im afraid :(
ASKER
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"
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) DESCASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ahh got ya, I fixed it now. was doing something stupidly wrong/
thanks for your time.
thanks for your time.
HAVING totalSpend>0 .....