12,075,066 members (65,444 online)
Rate this:
Please Sign up or sign in to vote.
See more: , +
Hi all I have a table which contains the name of person and item he has(1-50 range values). means two columns only.Now I have to select as :

<big>Name qty(1-10) qty(10-20) qty(20-30) qty(30-40) qty(40-50)
A 2
B 38
C 13
D 7</big>
Note: Here value is the exact quantity a person have.

I want to ask how to perform this in a single query. I know I can have multiple if else for the same column like if value is between 1- 10 then select it under first column else leave it blank.
Second is using multiple if else is okay or it is the worse practice.
Posted 14-Nov-12 17:35pm
shekhb279

## 2 solutions

Rate this:
Please Sign up or sign in to vote.

## Solution 1

```SELECT
Name,
SUM(CASE WHEN qty > 1  AND qty <= 10 THEN qty ELSE 0 END) as qty1_10,
SUM(CASE WHEN qty > 10 AND qty <= 20 THEN qty ELSE 0 END) as qty11_20,
SUM(CASE WHEN qty > 20 AND qty <= 30 THEN qty ELSE 0 END) as qty21_30,
SUM(CASE WHEN qty > 30 AND qty <= 40 THEN qty ELSE 0 END) as qty31_40,
SUM(CASE WHEN qty > 40 AND qty <= 50 THEN qty ELSE 0 END) as qty41_50
FROM Table1
GROUP BY Name
```
Comments
shekhb 14-Nov-12 23:13pm

Thanks Sir,
Thanks very much for the solution and I am happy to accept this as a solution but can you please comment on the performance of this query is there any other way of doing same which is more optimized. May these comments are due to lack of knowledge so need your guidance.

Thanks
Andrew Cherednik 14-Nov-12 23:25pm

I think if you have an index on Name, the query will work very fast
Rate this:
Please Sign up or sign in to vote.

## Solution 2

Use Case

```Syntax

CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:

DECLARE @TestVal INT --Declare a variable
SET @TestVal = 3 --Assiggning Variable value as 3

SELECT
CASE @TestVal
WHEN 1 THEN 'First' --@TestValvalue is 1 then 'First' return
WHEN 2 THEN 'Second'--@TestValvalue is 2 then 'Second' return
WHEN 3 THEN 'Third'--@TestValvalue is 3 then 'Third' return
ELSE 'Other'--@else then 'Other' return
END```

More details Refer
http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/[^]

http://msdn.microsoft.com/en-us/library/ms181765.aspx[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 656 Dave Kreskowiak 290 ProgramFOX 290 CHill60 160 Richard MacCutchan 159
 Dave Kreskowiak 2,796 OriginalGriff 2,685 Richard MacCutchan 1,834 CPallini 1,507 CHill60 1,379

Advertise | Privacy | Mobile
Web01 | 2.8.160208.1 | Last Updated 14 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100