SQL query for count all(unknown, known), unknown, known customer, identify uniquely by Phone Mac address

user_8631311 0
Viewed 104

Table 1(Customer Table)

Id, CustomerId, IsKnownCustomer,phonemacaddress

1, NULL 0 00:9a:34:cf:a4

2, 004024 1 00:6f:64:cf:a0:93

3, 004024 1 00:6f:64:cf:a0:93

4, 004003 1 2c:0e:3d:15:12:43

6, NULL 0 2c:0e:3d:15:125

7, 004003 1 34:f3:9a:ee:53:a9

8, 004023 1 38:0B:40:DC:BA:3A

9, NULL 0 4c:66:41:11:26:16

This is the table, and data is required for all customer, unknown customer and known customer.

output like: uniquely find the records by phonemacaddress.

count Customer,| customer type|
7 all

3 unknown customer

4 known customer

sql-server sql
4 Answers

You could use union and count

 select count(*), 'all'
 from customer
 union 
 select count(*), 'unknown customer'
 from customer
 where isKnowCustomer = 0
 union 
 select count(*), 'known customer'
 from customer
 where isKnowCustomer = 1
user_35223120

I would just put this in one row:

select count(*) as all,
       sum(IsKnownCustomer) as known,
       sum(1 - IsKnownCustomer) as unknown
from t;

I'm not sure why you would want three rows rather than three columns.

If you do, you can do:

select (case when isKnownCustomer = 0 then 'Unknown'
             when isKnownCustomer = 1 then 'Known'
             else 'All'
        end) as which, count(*)
from t
group by grouping sets ( (IsKnownCustomer), () )
user_11440350

Below SQL would help you.

SELECT  
    SUM(CASE WHEN Isknown = 1 THEN 1 ELSE 0 END) AS 'Known Customer',
    SUM(CASE WHEN Isknown = 0 THEN 1 ELSE 0 END) AS 'Unknown Customer',
    COUNT(*) AS 'All' 
FROM YourTable
user_70235030

You can aggregate the table with one scan, then use UNPIVOT.

SELECT [count customer], [customer type]
  FROM ( --aggregations will be placed side-by-side
         SELECT COUNT(*) [all]
               ,COUNT(CASE WHEN IsKnownCustomer = 0 THEN 1 END) [unknown customer]
               ,COUNT(CASE WHEN IsKnownCustomer = 1 THEN 1 END) [known customer]
          FROM CustomerTable
       ) AS dT
UNPIVOT( --unpivot, will separate aggregations by row
         [count customer] FOR [customer type] IN ([all], [unknown customer], [known customer])
       ) AS unp

Creates Output:

count customer  customer type
9               all
3               unknown customer
6               known customer

Here is the full code in SQL fiddle: http://sqlfiddle.com/#!6/54e54/2/0

user_30643450
Related