CODEWITHSUNDEEP
mysqlcountnullgroup-bydistinct
VaN
VaN

Reputation: 2210

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;

Upvotes: 2

Views: 1655

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

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;

OUTPUT:

+------------+---------------+--------+
| 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)

Upvotes: 1

Related Questions