HAVING totalSpend>0 .....

Solved

Posted on 2011-04-21

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

12 Comments

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

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

The viewer will learn how to dynamically set the form action using jQuery.

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

Connect with top rated Experts

**16** Experts available now in Live!