Boshqa maxsus satrda ma'lum bir elementga ega bo'lganida, SQL qanday aniq satrni sanash mumkin

1-jadval

ID     Name    Category
--------------------------------------
1      WASD     cat1
2      QWER     cat1
3      QWER     cat1
4      WASD     cat2
5      WASD     cat2
6      WASD     cat2

I want to be able to take Name and Category and count how many times cat1 & cat2 are presented within the table. But they both must be corresponding with WASD in the name row.

Misol uchun, men istagan chiqim shunday:

Cat1Amount    Cat2Amount
    1             3

Cat1 kodi faqat QWER bilan birga bo'lgan boshqa cat1 ning sababli bir marta paydo bo'lgani uchun ushbu chiqishni xohlayman. cat2 3 marta paydo bo'ladi, chunki WASD cat2 bilan 3 marta mavjud.

Hozirda bu so'rovlarimdan foydalanmoqdaman:

SELECT 
    Cat1Amount = SUM(CASE WHEN Category='cat1' THEN 1 ELSE 0 END),
    Cat2Amount = SUM(CASE WHEN Category='cat2' THEN 1 ELSE 0 END) 
FROM 
    dbo.Example

Masalan, WASD = va cat2 kodlari necha marta paydo bo'lishini ko'rsatib turibdi, mavjud.

1
SELECT nomi, ... va GROUP BY name ni qo'shishga harakat qildingizmi?
qo'shib qo'ydi muallif Salman A, manba

5 javoblar

Siz ham qilishingiz mumkin

SELECT SUM(CASE WHEN Category = 'cat1' AND Name = 'WASD' THEN 1 ELSE 0 END) Cat1Amount, 
       SUM(CASE WHEN Category = 'cat2' AND Name = 'WASD'  THEN 1 ELSE 0 END) Cat2Amount 
FROM Tbl
1
qo'shib qo'ydi
kod ssas mdx kub tomonidan iste'mol qilinmaguncha, bu faktlarni/dim uslubini agregatlarni qilishning afzal usulim edi.
qo'shib qo'ydi muallif junketsu, manba

You need where clause & do aggregation :

SELECT SUM(CASE WHEN Category = 'cat1' THEN 1 ELSE 0 END), 
       SUM(CASE WHEN Category = 'cat2' THEN 1 ELSE 0 END) 
FROM dbo.Example e
WHERE Name = 'WASD';
1
qo'shib qo'ydi
Ha, bu bir tonna uchun rahmat!
qo'shib qo'ydi muallif Blake, manba

Quyidagilarni sinab ko'ring

SELECT name,count(CASE WHEN Category = 'cat1' THEN 1 end) as cat1amount, 
       count(CASE WHEN Category = 'cat2' THEN 1 END) as cat2amount
FROM dbo.Example e
WHERE Name = 'WASD'
group by name
0
qo'shib qo'ydi
SELECT COUNT(Category)
FROM table_name
WHERE name = 'WASD' category = 'cat1';

SELECT COUNT(Category)
FROM table_name
WHERE name = 'WASD' category = 'cat2';
0
qo'shib qo'ydi
Ushbu kod javobga javob berishi mumkin bo'lsa-da, savolning qanday va/yoki nima sababdan hal qilinayotganiga doir qo'shimcha kontekstni berish javobning uzoq muddatli qiymatini yaxshilashga olib keladi.
qo'shib qo'ydi muallif Nic3500, manba
Select Category, count(ID) as counted
From Table#1 a
where a.Name = 'WASD'
Group by Category

Natija quyidagicha bo'ladi:

Turkum hisoblangan

cat1 1

cat2 3

0
qo'shib qo'ydi