VaN VaN - 5 months ago 7
MySQL Question

Count group by field if not null

I have the following 2 tables :

CALLS :

id | date | user_id
-------------------------
1 | 2016-06-22 | 1
2 | 2016-06-22 | NULL
3 | 2016-06-22 | NULL
4 | 2016-06-23 | 2
5 | 2016-06-23 | 1
6 | 2016-06-23 | 1
7 | 2016-06-23 | NULL


PAYMENTS :

id | date | user_id | value
---------------------------------
1 | 2016-06-22 | 1 | 10
2 | 2016-06-22 | 3 | 15
3 | 2016-06-22 | 4 | 20
4 | 2016-06-23 | 2 | 100
5 | 2016-06-23 | 1 | 150


I'm trying to build a query to retrieve the following output :

date | distinct_calls | income
------------------------------------
2016-06-22 | 1 | 10
2016-06-22 | NULL | 0
2016-06-22 | NULL | 0
2016-06-23 | 2 | 250
2016-06-23 | NULL | 0


The output should count the DISTINCT USER_ID from the CALLS table and group them by DATE, but only if USER_ID is not null. If this field is null, each null should stand as 1 row. That's why there are 3 output rows for 2016-06-22, 2 of them with NULL calls. And I'm also trying to add a third output column, containing the sum of the VALUE field from the PAIMENTS table for this day. But this sum should take in consideration only the paiments whose USER_ID is present in the CALLS table for this given day. That's why the income value for the first output row is 10 instead of 10+15+20, because USER_ID 3 and 4 are not present in the CALLS table for this date.

Right now, my query looks like this :

SELECT calls.date, IF(calls.user_id IS NULL, NULL, COUNT(calls.id)) AS distinct_calls
FROM calls
GROUP BY calls.date, IF(calls.user_id IS NULL, calls.id, "not null")


But this query produce the following output :

date | distinct_calls
---------------------------
2016-06-22 | NULL
2016-06-22 | NULL
2016-06-22 | 1
2016-06-23 | NULL
2016-06-23 | 3


Which is wrong. Should instead have

date | distinct_calls
---------------------------
2016-06-22 | NULL
2016-06-22 | NULL
2016-06-22 | 1
2016-06-23 | NULL
2016-06-23 | 2


Any help would be appreciated to correct this first output, and then add this 3rd income column value.

Here is the tables raw data :

--
-- Structure de la table `calls`
--

CREATE TABLE IF NOT EXISTS `calls` (
`id` int(11) NOT NULL,
`date` date NOT NULL,
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `calls`
--

INSERT INTO `calls` (`id`, `date`, `user_id`) VALUES
(1, '2016-06-22', 1),
(2, '2016-06-22', NULL),
(3, '2016-06-22', NULL),
(4, '2016-06-23', 2),
(5, '2016-06-23', 1),
(6, '2016-06-23', 1),
(7, '2016-06-23', NULL);

-- --------------------------------------------------------

--
-- Structure de la table `payments`
--

CREATE TABLE IF NOT EXISTS `payments` (
`id` int(11) NOT NULL,
`date` date NOT NULL,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `payments`
--

INSERT INTO `payments` (`id`, `date`, `user_id`, `value`) VALUES
(1, '2016-06-22', 1, 10),
(2, '2016-06-22', 3, 15),
(3, '2016-06-22', 4, 20),
(4, '2016-06-23', 2, 100),
(5, '2016-06-23', 1, 150);

--
-- Index pour les tables exportées
--

--
-- Index pour la table `calls`
--
ALTER TABLE `calls`
ADD PRIMARY KEY (`id`);

--
-- Index pour la table `payments`
--
ALTER TABLE `payments`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT pour les tables exportées
--

--
-- AUTO_INCREMENT pour la table `calls`
--
ALTER TABLE `calls`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT pour la table `payments`
--
ALTER TABLE `payments`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;

Answer

You can get it using following two queries

select p.date,count() as distinct_call,sum(p.value) as income from payments p inner join (select id,date,user_id,count() from calls where user_id is not null group by date,user_id) c on c.user_id=p.user_id and c.date=p.date group by p.date;

+------------+---------------+--------+
| date       | distinct_call | income |
+------------+---------------+--------+
| 2016-06-22 |             1 |     10 |
| 2016-06-23 |             2 |    250 |
+------------+---------------+--------+

2 rows in set (0.00 sec)

mysql> select date,user_id as distinct_call,0 as income from calls where user_id is null;

+------------+---------------+--------+
| date       | distinct_call | income |
+------------+---------------+--------+
| 2016-06-22 |          NULL |      0 |
| 2016-06-22 |          NULL |      0 |
| 2016-06-23 |          NULL |      0 |
+------------+---------------+--------+

3 rows in set (0.00 sec)

Comments