Tableau 1 (Tableau des clients)
Id, CustomerId, IsKnownCustomer, adresse de téléphone
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
C'est la table et datatables sont requirejses pour tous les clients, clients inconnus et clients connus.
sortie comme: find uniquement les loggings par phonemacaddress.
count client, | type de client |
7 tous
3 client inconnu
4 clients connus
Vous pouvez utiliser union et countr
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
Je voudrais juste mettre cela en une ligne:
select count(*) as all, sum(IsKnownCustomer) as known, sum(1 - IsKnownCustomer) as unknown from t;
Je ne suis pas sûr pourquoi vous voudriez trois rangées plutôt que trois colonnes.
Si vous le faites, vous pouvez faire:
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), () )
Ci-dessous SQL vous aiderait.
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
Vous pouvez agréger la table avec une parsing, puis utiliser 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
Crée une sortie:
count customer customer type 9 all 3 unknown customer 6 known customer
Voici le code complet dans SQL fiddle: http://sqlfiddle.com/#!6/54e54/2/0