SQL Counting the Count Results

Question

I'm trying to generate reports from sales that I want to show in data studio. I have column email, order_number and total_price and I'm trying to identify the following using sql query:

1. Number of repeating and new customer (counting number of orders, if order > 1 then repeating, else new)
2. Number of orders from repeating and new customers

    Date     | email             | Order_number | Total Price 
---------------------------------------------------------------
    101019   | [email protected]  | 1111         | 100         
    101019   | [email protected]  | 1112         | 200        
    111019   | [email protected]  | 1113         | 130         
    111019   | [email protected]  | 1111         | 100  

What I did is only to count the number of orders each email

SELECT email, count(email) as order_count, date FROM orders GROUP BY 
email

This is sample report that I want to show

    Repeating Customer | New Customer  
        1              |       2


    Orders from Repeating Cust. | Orders from New Customer
          2                     |    2


    Total Price Repeating Cust. | Total Price New Custo.
          200                   |      330

Any help is greatly appreciated.

Answer 1

You may try Case and Sub query:

SELECT SUM(CASE WHEN t.Cust_cnt=1 THEN 1 ELSE 0 END) AS New_Cust,
       SUM(CASE WHEN t.Cust_cnt>1 THEN 1 ELSE 0 END) AS Repeat_Cust,
       SUM(CASE WHEN t.Cust_cnt=1 THEN t.Total_Order ELSE 0 END) AS New_Cust_Order,
       SUM(CASE WHEN t.Cust_cnt>1 THEN t.Total_Order ELSE 0 END) AS Repeat_Cust_Order,
       SUM(CASE WHEN t.Cust_cnt=1 THEN t.Total_Price ELSE 0 END) AS New_Cust_Order_Total_Price,
       SUM(CASE WHEN t.Cust_cnt>1 THEN t.Total_Price ELSE 0 END) AS Repeat_Cust_OrderTotal_Price,
       t.Last_Order_Date
FROM (
SELECT COUNT(Email) AS Cust_cnt,SUM(O_Price) AS Total_Price,COUNT(O_Number) AS Total_Order, MAX(O_Date) AS Last_Order_Date, Email  FROM Orders Group by Email) AS t;