CODEWITHSUNDEEP
mysqlcountgroup-by
lxsparks
lxsparks

Reputation: 81

Getting COUNT while ignoring GROUP BY

I have the following table: ProductSales

+-------+-----------+--------+-----------+
|prod_id|customer_id|order_id|supplier_id|
+-------+-----------+--------+-----------+
|   1   |     1     |   1    |     1     |
+-------+-----------+--------+-----------+
|   2   |     4     |   2    |     2     |
+-------+-----------+--------+-----------+
|   3   |     1     |   1    |     1     |
+-------+-----------+--------+-----------+
|   4   |    NULL   |  NULL  |   Null    |
+-------+-----------+--------+-----------+
|   5   |     1     |   1    |     2     |
+-------+-----------+--------+-----------+
|   6   |     4     |   7    |     1     |
+-------+-----------+--------+-----------+
|   7   |     1     |   1    |     3     |
+-------+-----------+--------+-----------+

I have a SELECT query:

SELECT customer_id AS customer, count(*) AS prod_count
     , count(DISTINCT order_id) as orders 
  FROM ProductSales 
 WHERE supplier_id=1        
 GROUP BY customer_id  
HAVING customer_id<>'NULL'

This will be produce the result:

+--------+----------+------+
|customer|prod_count|orders|
+--------+----------+------+
|   1    |    2     |   1  |
+--------+----------+------+
|   4    |    1     |   1  |
+--------+----------+------+

What I have been trying to achieve and getting nowhere is to add a fourth column in my results to show the number of order_ids that belong only to the current supplier for each customer:

+--------+----------+------+-------------+
|customer|prod_count|orders|Unique Orders|
+--------+----------+------+-------------+
|   1    |    2     |   1  |      0      | } Order '1' is connected with two supplier_ids
+--------+----------+------+-------------+
|   4    |    1     |   1  |      1      | } Order '2' is connected to only one supplier_id
+--------+----------+------+-------------+

(This gets more complex when there are more orders per customer associated with far more suppliers).

I thought I was close with: 

SELECT t1.user_id, count(DISTINCT t1.prod_id) AS prod_count
     , count(DISTINCT t1.order_id) as orders
     , IF(count(DISTINCT t3.supplier_id)>1,0,1) AS Unique_Orders
  FROM ProductSales AS t1
  LEFT JOIN `order` AS t2 ON t1.order_id=t2.order_id
  LEFT JOIN ProductSales AS t3 ON t2.order_id=t3.order_id
 WHERE t1.supplier_id=1
 GROUP BY t1.customer_id
HAVING t1.customer_id<>'NULL'

The orders table stated above is related to ProductSales only by order_id.

Which shows my Customers, Products(total), Orders(total) but the Unique Orders shows if there are unique orders (0) or not (1), I understand the logic of the IF statement and it does what I expect. It's working out how to find the number of unique orders which is baffling me.

The table is established and can't be changed.

Any suggestions?

Upvotes: 1

Views: 1666

Answers (1)

podiluska
podiluska

Reputation: 51494

Unique orders can be defined as 

SELECT OrderID
FROM yourtable
GROUP BY OrderID
Having COUNT(Distinct SupplierID) = 1

So try 

SELECT 
    customer_id AS customer, 
    count(*) AS prod_count.
    count(DISTINCT productsales.order_id) as orders,
    COUNT(distinct uqo)
FROM ProductSales  
    left join 
    (
        SELECT Order_ID uqo
        FROM Productsales
        GROUP BY Order_ID 
        Having COUNT(Distinct supplier_id) = 1 
    ) uniqueorders 
    on ProductSales.order_id = uniqueorders.uqo      
 WHERE supplier_id=1              
 GROUP BY customer_id

Upvotes: 2

Related Questions