CODEWITHSUNDEEP
sqljoincountoledbdistinct
Zoltan
Zoltan

Reputation: 63

Select and count uniqe value where gender = X

I have a sql query :

SELECT t1.Nev, 
       Count(t2.Datum) AS Edzes_szam 
FROM   Adatok t1 
       INNER JOIN (SELECT DISTINCT Kod, 
                                   Datum 
                   FROM   Jelenlet 
                   WHERE  Datum LIKE '" + dtm_year_hkszpp_ym + "%') t2 
               ON t1.Azon_sima = t2.Kod 
GROUP  BY t1.Nev

I would like to ask your help to change it for only gender='X' have to be counted.

This query count for a name how many times was it logged in a selected month. I would like to make a filter for gender. How and where i have to take this where gender = 'X'.

I'm using MSOLEDB.

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You don't need the subquery for what you are doing:

SELECT A.Nev, Count(DISTINCT J.Datum) AS Edzes_szam 
FROM Adatok a INNER JOIN
     Jelenlet j
     ON A.Azon_sima = j.Kod AND
        j.Datum LIKE '" + dtm_year_hkszpp_ym + "%'
GROUP BY a.Nev ;

With this structure, you can just add WHERE gender = <whatever> before the GROUP BY.

Your code sort of suggests that you are using LIKE for dates. This is not recommended in any database. All have built-in date and time functions that are better than LIKE.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

Its too simple. If you have gender column in Adatok table then, Try it:-

SELECT t1.Nev, 
   Count(t2.Datum) AS Edzes_szam 
FROM   Adatok t1 
   INNER JOIN (SELECT DISTINCT Kod, 
                               Datum 
               FROM   Jelenlet 
               WHERE  Datum LIKE '" + dtm_year_hkszpp_ym + "%') t2 
           ON t1.Azon_sima = t2.Kod 
WHERE t1.gender = 'X'
GROUP  BY t1.Nev

Otherwise, Try this:-

SELECT t1.Nev, 
   Count(t2.Datum) AS Edzes_szam 
FROM   Adatok t1 
   INNER JOIN (SELECT DISTINCT Kod, 
                               Datum 
               FROM   Jelenlet 
               WHERE  Datum LIKE '" + dtm_year_hkszpp_ym + "%'
               AND gender = 'X') t2 
           ON t1.Azon_sima = t2.Kod 

GROUP  BY t1.Nev

Upvotes: 0

Related Questions