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.

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";
```

```
$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";
```

```
$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";
```

@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.

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
```

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.

try on query analyser and check whether it fails

Open in new window