CODEWITHSUNDEEP
mysqlsqlstringwhere-clausesql-like
rawaffamo4407
rawaffamo4407

Reputation: 13

Mysql - sum record where word like

i have table in phpmyadmin looks like:

id_perfume, name_perfume, id_brand, family_smell,   price
1            For 1        11        flower-oriental 595
2            For 2        11        flower          435
3            Edre alouse  12        wooden          368

I want in output sum of price all perfume where contain in smell family word flower.

sum family flower
1030

I tried this query, it doesn't work because in output display 0.

SELECT COUNT(price) from perfume WHERE family_smell like "flower*";

Upvotes: 1

Views: 254

Answers (1)

GMB
GMB

Reputation: 222612

The wildcard character for pattern matching is %, not *. Also, if you want a sum, then use sum(), not count().

So:

select sum(price) sum_price from perfume where family_smell like 'flower%'

This phrases as: sum the prices of all rows where family_smell starts with "flower". If you wanted contains "flower", then you would put a wildcard on both ends:

where family_smell like '%flower%'

Note that I used single quotes instead of double quotes to surround the literal string. This complies to the SQL standard. In many other databases (and in ANSI SQL as well), double quotes stand for identifiers (such as column or table names), not for strings.

Upvotes: 2

Related Questions