/    Sign up×
Community /Pin to ProfileBookmark

MySQL – replace SUM/COUNT zero with –

I’m doing several SQL searches, using both COUNT and SUM methods.

When a number is 0 I want (need) to display a hyphen –

Is there a way I can do this in my SQL vs in PHP output. It’s used in numerous places so would be quicker if my DB was outputting it vs doing a PHP if statement.

to post a comment
PHP

4 Comments(s)

Copy linkTweet thisAlerts:
@NogDogFeb 28.2021 — Hmm...gets a bit ugly within SQL due to "-" being a character or text type, while presumably the count/sum values would be either integers or floats. I feel like it might be cleaner to handle in the PHP, maybe using a ternary operator to make it a bit more terse:
[code=php]
echo $count == 0 ? '-' : $count;
[/code]

However, you _might_ be able to do something like this in the SQL, but I'm not crazy about it:
[code=sql]
select
some_id,
case
when the_count == 0 then '-'
else cast(the_count as char) -- so that the type is the same
end as final_count
from (
select some_id, count(some_column) as the_count
from some table
group by some_id
) as first_query
[/code]
Copy linkTweet thisAlerts:
@kiwisauthorFeb 28.2021 — @NogDog#1628660

Yeah that's what I had thought of.

Okay, next issue. I'm looping through my results and building a string of which will be an output of HTML.

For example...

while ($row = $result-&gt;fetch_assoc()) { <br/>
$html .= "&lt;td&gt;" . $row['myCountValue'] . "&lt;/td&gt;";<br/>
}


If there a way to switch between outputting the value and the hyphen when building a string. I think the approach above doesn't work. An if statement also means you need to exit the string and rebuild it again.
Copy linkTweet thisAlerts:
@NogDogFeb 28.2021 — I'd probably just do:
[code=php]
$html .= "<td>" . ($row['myCountValue'] == 0 ? "-" : $orw['myCountValue']) . "</td>";
[/code]

...or else I'd make a little function to keep the output code cleaner:
[code=php]
$html .= "<td>" . dashOnZero($row['myCountValue']) . "</td>";

// wherever you want to define the function...
function dashOnZero($value) {
return $value == 0 ? "-" : $value;
}
[/code]
Copy linkTweet thisAlerts:
@kiwisauthorFeb 28.2021 — Didn't know you could do a function like that when appending text together
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.29,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...