Count based on condition

Question

Count based on condition

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 19, 2021 at 2:17 am
Go to Answer
#3965167
Hi
I have below query . In T1.Sample1,T1.Sample 'Y' & 'N' is stored . I want to display Count of T1.Sample1 which have & and similarly of T1.Sample2
```
SELECT 
T1.U_VendCode,T1.U_VendName as "Vendor Name",T1.Sample1,T1.Sample2
FROM [tbl1] T0
inner join [tbl2] T1 on T0.Docentry = T1.DocEntry
where (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' )
group by T1.U_VendCode,T1.U_VendName,,T1.Sampl1,T1.Sample2
```
Code Name Sample1 Sample2
1 A Y N
1 A Y Y
1 A N Y
1 A Y Y
In above scenario 3 records will get display.
1 A 2 2
1 A 0 1
1 A 1 0
Thanks
- This topic was modified 2 years, 11 months ago by jagjitsingh.

Jeff Moden

SSC Guru

Points: 1004324
More actions
December 19, 2021 at 3:40 am
Answer
#3965179
jagjitsingh wrote:
Hi Jeff
Below is the data
CREATE TABLE Persons (LastName varchar(255),FirstName varchar(255),Sampl11 varchar(255),Sample2 varchar(255)); Insert Into Persons values('1','A','Y','N')Insert Into Persons values('1','A','Y','Y')Insert Into Persons values('1','A','N','Y')Insert Into Persons values('1','A','Y','Y')
Thanks
In the same thoughtful spirit the data was offered in, below is the solution. 😉
```
SELECT LastName,FirstName,Sampl11=SUM(IIF(Sampl11='Y',1,0)),Sample2=SUM(IIF(Sample2='Y',1,0))FROM dbo.Persons GROUP BY LastName,FirstName,Sample2,Sampl11 ORDER BY LastName,FirstName,Sample2 DESC,Sampl11;
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

This is pretty simple but you've been asked to provide readily consumable data before and it would mighty nice if you'd start to help us help you.

Without such readily consumable data (again, see the article at the first link in my signature line below for one way to provide such a thing and well as having been shown many times in the past how to do such thing), I'll just tell you to do the obvious conversion of Y's to 1's and N's to 0's and do the sum with the Group By on the obvious columns in a CROSSTAB-like fashion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 2

Hi Jeff

Below is the data

CREATE TABLE Persons (LastName varchar(255),FirstName varchar(255),Sampl11 varchar(255),Sample2 varchar(255)); Insert Into Persons values('1','A','Y','N')Insert Into Persons values('1','A','Y','Y')Insert Into Persons values('1','A','N','Y')Insert Into Persons values('1','A','Y','Y')

Thanks

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Please read: Forum Etiquette: How to post data/code on a forum to get the best help

You can script table creation and inserts and copy them to the clipboard and then just hit the INSERT CODE SAMPLE button and drop your scripts right in to the window that opens.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

pietlinden wrote:

Please read: Forum Etiquette: How to post data/code on a forum to get the best help
You can script table creation and inserts and copy them to the clipboard and then just hit the INSERT CODE SAMPLE button and drop your scripts right in to the window that opens.

Especially the part about having all code on a single line, huh? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 5

Hi Jeff

How the below code works

Sampl11=SUM(IIF(Sampl11='Y',1,0))

Thanks

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

jagjitsingh wrote:

Hi Jeff
How the below code works
Sampl11=SUM(IIF(Sampl11='Y',1,0))
Thanks

With the idea of teaching a man to fish, if you want to become successful in SQL Server and T-SQL, documentation is your friend. Try the following search in one of the many search engines available.

iif sqlserver

Then, do the same for SUM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)