Counting distinct field in table2 from distinct ids in table1 in SQL (MySQL)
I have 2 tables:
Table 1:
| order_id | shop_id | customer_id | total | date |
-------------------------------------------------------
| 9005 | A | 1 开发者_运维技巧 | 1205 | 20110210 |
| 9006 | A | 2 | 8591 | 20110212 |
| 9007 | A | 2 | 3472 | 20110216 |
| 9008 | B | 1 | 6310 | 20110218 |
-------------------------------------------------------
Table 2:
| shop_id | customer_id | reference |
-------------------------------------
| A | 1 | Friend |
| A | 2 | Internet |
| B | 1 | Friend |
| C | 1 | Friend |
-------------------------------------
I want to select distinct values (shop_id, customer_id) from table1 (filtered by date) and then count the references at table2.
Expected result:
| reference | count |
---------------------
| Friend | 2 |
| Internet | 1 |
----------------------
So far, the Query that I'm using is:
SELECT reference,COUNT(*) as count FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY reference
And the result is:
| reference | count |
---------------------
| Friend | 2 |
| Internet | 2 |
---------------------
The problem is that is counting 2 times: shop_id=A and customer_id=2. That is why "Internet" is counted twice.
Could someone help me to identify what is wrong? If possible, I would like to achieve this without using sub-queries (technical limitation).
Thank you.
SQL Dump:
CREATE TABLE `table1` (
`order_id` int(11),
`shop_id` char(1),
`customer_id` int(11),
`total` smallint(6),
`date` date
);
INSERT INTO `table1` (`order_id`, `shop_id`, `customer_id`, `total`, `date`) VALUES
('9005', 'A', '1', '1205', '2011-02-10'),
('9006', 'A', '2', '8591', '2011-02-12'),
('9007', 'A', '2', '3472', '2011-02-16'),
('9008', 'B', '1', '6310', '2011-02-18');
CREATE TABLE `table2` (
`customer_id` int(11),
`shop_id` char(1),
`reference` enum('Friend','Internet')
);
INSERT INTO `table2` (`customer_id`, `shop_id`, `reference`) VALUES
('1', 'A', 'Friend'),
('2', 'A', 'Internet'),
('1', 'B', 'Friend'),
('1', 'C', 'Friend');
I think this might work for you - at least it returns the expected result
SELECT reference,COUNT(distinct(concat(shop_id,'_',customer_id))) as count
FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY reference;
The '_' is to avoid mixing store_id and customer_id, you can probably use a different hashing function to generate unique store/customer ID
This is one solution using sub-queries:
SELECT reference, COUNT(*) as count FROM (SELECT shop_id,customer_id,reference FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY shop_id,customer_id) as filtered GROUP BY(reference)
But I would prefer not using sub-queries... If there is not such an alternative, this will become the correct answer.
精彩评论