MySQL query help

sjaq · 05-03-2009, 09:43 AM

Hello again,

I'm working on this file upload tinyurl like thing and have some problems with one mysql query. There are 2 tables with "id"s and I want to count all data and views in those tables with a mysql query. But when there are no records for the specified user_id in one of the tables the views is set to NULL and I want an integer. I've looked at CAST and CONVERT but can't seem to get it to work.

This is my current query:

Code:

SELECT datax + datay AS data, viewsx + viewsy as views FROM
(SELECT COUNT(*) AS datax, SUM(views) as viewsx FROM `data_id_custom` WHERE `user_id` = 1) as x,
(SELECT COUNT(*) AS datay, SUM(views) as viewsy FROM `data_id` WHERE `user_id` = 1) as y

Hope this is enough information to get some help

sjaq

Wildhoney · 05-03-2009, 02:53 PM

The way to solve this is to modify the database. For the views column, set it so it cannot be NULL, but also the default value of that column is 0.

That way you will have no NULLs in your database to mess up the counting, instead it will be populated with zeroes where there are no views.

sjaq · 05-03-2009, 08:02 PM

The views row already defaults to 0. The problem is when one of the SELECTS has no results e.g. there are no items in data_id_custom for user_id 12 so SUM(views) can't count any data.

This is the structure for both tables:

Salathe · 05-03-2009, 08:44 PM

Perhaps look into using the COALESCE function in your query.

sjaq · 05-04-2009, 10:33 AM

Thank you Salathe, this works:

Code:

SELECT datax + datay AS data, viewsx + viewsy as views FROM
(SELECT COUNT(*) AS datax, COALESCE(SUM(views), 0) as viewsx FROM `data_id_custom` WHERE `user_id` = 1) as x,
(SELECT COUNT(*) AS datay, COALESCE(SUM(views), 0) as viewsy FROM `data_id` WHERE `user_id` = 1) as y