Little explanation of this mysql code

Question

I got this answer from somebody on another Q before, but I would like to get an explanation of this, so that I fully understand the problem.

I have a table, with a column which contains a value of either "private" or "company". Thing is, I want to check how many "private" and "company" values are in my records, so that I can display them with the search results later on. So if there are 4 company ads, and 1 private ad, this is the results:

  Company = 4
  Private = 1
  All ads = 5

And here is the code I got from someone about how to do this, in other words, this is what I would like explained:

SELECT
    IFNULL( field , 'All ads' ) AS 'Type',
    COUNT( * )
FROM
    `table`
GROUP BY
    field
WITH ROLLUP

Thanks

Answer 1

Select the Type field, and if the value is null, use All Ads as the default value.

Select them from the table, and group them by field.

As far as I can tell, this will count each the number of entries of each 'Type', and all the entries that have a null value will simply go under the 'All Ads' count.

---

Just BTW, if you look at the IFNULL() function, it is pretty easy to figure out. It clearly states IF NULL, so it is checking if something is null. Then you pass it a field name and a static value. Kind of makes sense that it is checking if that field value is false. And the only other thing there is the value, so we can come to the conclusion that it will use the static value, if the field value is null.

Answer 2

Selects all adverts from the table "table", it groups them by field, this means that all the ads that are "public" are placed into a group "public" with an associated number. So if you have 10 public adverts they're all grouped into "public adverts" with the value 10.

Does this make sense?

Answer 3

I assume that the part you don't understand is the ROLLUP clause, which is not often used.

The manual describes it well, but the basic idea is that each group is aggregated, and then you get one extra group at the end which aggregates the rows from all groups, and where Field is set to NULL.

The IFNULL expression changes the NULL to a readable string instead. You could use COALESCE instead of IFNULL to get the same effect.