Prev: SQlServer2008\DateStoredAsVarChar\QueryHelp
Next: Help on simple SQL statement
From: Himansu on 11 May 2010 17:09
Hello everyone,

Here is a sample table I have. I need to group by the product and display
by a range.


|ProductA | ProductB| ProductC| ProductD| ProductE|
---------------------------------------------------------------------------------
| 1 | 5 | 7 | 6 | 3 |
|_______________|_______________|_______________|_______________|_______________|
| 6 | 7 | 1 | 3 | 5 |
|_______________|_______________|_______________|_______________|_______________|
| 4 | 2 | 4 | 8 | 9 |
|_______________|_______________|_______________|_______________|_______________|
| 3 | 0 | 6 | 7 | 1 |
|_______________|_______________|_______________|_______________|_______________|
| 8 | 6 | 7 | 3 | 4 |
|_______________|_______________|_______________|_______________|_______________|
| 8 | 2 | 5 | 6 | 7 |
|_______________|_______________|_______________|_______________|_______________|
| 7 | 0 | 4 | 7 | 6 |
|_______________|_______________|_______________|_______________|_______________|
| 7 | 2 | 5 | 6 | 6 |
|_______________|_______________|_______________|_______________|_______________|


----

I need some SQL help to display the results as:

-----------------------------------------------------------------
|Type | 0 - 2 | 3- 5 | 6 - 9 |
-----------------------------------------------------------------
|ProductA | 13 | 3 | 9 |
|_______________|_______________|_______________|_______________|
|ProductB | 9 | 5 | 4 |
|_______________|_______________|_______________|_______________|
|ProductC | 34 | 66 | 2 |
|_______________|_______________|_______________|_______________|
|ProductD | 67 | 99 | 66 |
|_______________|_______________|_______________|_______________|
|ProductE | 9 | 8 | 8 |
|_______________|_______________|_______________|_______________|




Thanks,
Himansu


From: Plamen Ratchev on 11 May 2010 17:29
You have to unpivot and then group with CASE expressions:

SELECT product,
SUM(CASE WHEN value BETWEEN 0 AND 2 THEN value ELSE 0 END) AS
total0_2,
SUM(CASE WHEN value BETWEEN 3 AND 5 THEN value ELSE 0 END) AS
total3_5,
SUM(CASE WHEN value BETWEEN 6 AND 9 THEN value ELSE 0 END) AS
total6_7
FROM Products AS P
UNPIVOT
(value FOR product IN (ProductA,
ProductB,
ProductC,
ProductD,
ProductE)) AS U
GROUP BY product;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Himansu on 11 May 2010 18:17
Thanks, Plamen.

I need to actually count the occurances of the ranges. So if 0-2 range
falls into ProdductA then it need to count. Here's a specific example:

|ProductA | ProductB| ProductC| ProductD| ProductE|
---------------------------------------------------------------------------------
| 1 | 5 | 7 | 6 | 3 |
|_______________|_______________|_______________|_______________|_______________|
| 6 | 7 | 1 | 3 | 5 |
|_______________|_______________|_______________|_______________|_______________|
| 4 | 2 | 4 | 8 | 9 |
|_______________|_______________|_______________|_______________|_______________|
| 3 | 0 | 6 | 7 | 1 |
|_______________|_______________|_______________|_______________|_______________|
| 8 | 6 | 7 | 3 | 4 |
|_______________|_______________|_______________|_______________|_______________|
| 8 | 2 | 5 | 6 | 7 |
|_______________|_______________|_______________|_______________|_______________|
| 7 | 0 | 4 | 7 | 6 |
|_______________|_______________|_______________|_______________|_______________|
| 7 | 2 | 5 | 6 | 6 |
|_______________|_______________|_______________|_______________|_______________|


ProductA should have 1 for total0-2, 2 total3_5 , 5 total6_9


Hope this helps.

Thanks and have a nice evening.
Himansu


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:k0jju55jl6g1rtq683nvt6qd14l21bgiih(a)4ax.com...
> You have to unpivot and then group with CASE expressions:
>
> SELECT product,
> SUM(CASE WHEN value BETWEEN 0 AND 2 THEN value ELSE 0 END) AS
> total0_2,
> SUM(CASE WHEN value BETWEEN 3 AND 5 THEN value ELSE 0 END) AS
> total3_5,
> SUM(CASE WHEN value BETWEEN 6 AND 9 THEN value ELSE 0 END) AS
> total6_7
> FROM Products AS P
> UNPIVOT
> (value FOR product IN (ProductA,
> ProductB,
> ProductC,
> ProductD,
> ProductE)) AS U
> GROUP BY product;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: --CELKO-- on 11 May 2010 19:57
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

You have made two fundamental errors.
1) You have a repeated group, so your "table" is not even in First
Normal Form.
2) You do display formatting in the front end of a tiered
architecture, not in the database.

Can you fix the schema?

From: Plamen Ratchev on 11 May 2010 21:45
Simply change the SUM to use 1 instead of value:

SELECT product,
SUM(CASE WHEN value BETWEEN 0 AND 2 THEN 1 ELSE 0 END) AS
total0_2,
SUM(CASE WHEN value BETWEEN 3 AND 5 THEN 1 ELSE 0 END) AS
total3_5,
SUM(CASE WHEN value BETWEEN 6 AND 9 THEN 1 ELSE 0 END) AS
total6_7
FROM Products AS P
UNPIVOT
(value FOR product IN (ProductA,
ProductB,
ProductC,
ProductD,
ProductE)) AS U
GROUP BY product;

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: SQlServer2008\DateStoredAsVarChar\QueryHelp
Next: Help on simple SQL statement