Grouping two similar column names but different data?

Hi All,
I have a need to group a column with he same name.
I have a column called "AccountType" which has data such as :
A1
A2
A3
A4
I am using an aggrate for this column:
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
I want to also group by the actual group type. Something like:
SELECT
AccountType,
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType, AccountType
Can someone please give me a little help with this?
Thanks very much,
John.John,
Can you post an example of the expected result?
AMB
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||John:
without knowing exactly what you want, its difficult to answer.
Is this what you want:
select t.[account type], count(*)
from (
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
) t
group by t.[Account Type]
If not then try posting some sample data set and the required output and i
am sure someone will be able to help you on that.
just incase if you wanna play around and understand what the above code is
doing then use northwind and execute this query
use northwind
go
select t.lessOrMore, count(*) , sum(t.OrderCount)
from (
select orderID, count(*) as OrderCount
, case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
from [Order Details]
group by OrderID ) t
group by t.LessOrMore
Hope the above helps
Abhishek
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||My current data result is something like this:
LastName | Account Type | NumCount
Miller | Good | 20
Miller | Not Good | 5
Jones | Not Good | 37
Miller | Not Good | 9
What I would like to see is the following:
LastName | Account Type Actual Type | NumCount
Miller | Good | A1 |
20
Miller | Not Good | A2 | 5
Jones | Not Good | A3 | 37
Miller | Not Good | A4 |
9
In the first example I am grouping by LastName, [Account Type]
In the second example I need to Group by the same and addition to the Actual
Account Type.
The problem here though is that the column "AccountType" needs to be used
twice and I don't know how to handle this. Unfortunately I can not use a
unique alias for each one that can be Grouped.
John.
"Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
> John:
> without knowing exactly what you want, its difficult to answer.
> Is this what you want:
> select t.[account type], count(*)
> from (
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account
> Type]
> FROM Tbl1
> GROUP BY AccountType
> ) t
> group by t.[Account Type]
>
> If not then try posting some sample data set and the required output and i
> am sure someone will be able to help you on that.
> just incase if you wanna play around and understand what the above code is
> doing then use northwind and execute this query
> use northwind
> go
> select t.lessOrMore, count(*) , sum(t.OrderCount)
> from (
> select orderID, count(*) as OrderCount
> , case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
> from [Order Details]
> group by OrderID ) t
> group by t.LessOrMore
>
> Hope the above helps
> Abhishek
> "John" wrote:
>|||John:
It seems you dont need a second groupby.. coz you are not doing another
group by. It seems you just need and extra column. This is what is reflected
in the result set you posted (NumCount remains the same and you just need an
extra column for actual account type)
But then again you will need to be more clear in what exacly you want
is this what you want:
LastName | Account Type | Actual Type | NumCount
Miller | Good | A1 | 20
Miller | Not Good | A2 | 3
Miller | Not Good | A3 | 2
Jones | Not Good | A3 | 30
Jones | Not Good | A4 | 7
Miller | Not Good | A4 | 9
Notice that for miller not good account i have further divided into 2 actual
account type and the sum of count 3+2 = 5.
similarly for Jones it is 30+7 = 37.
If above is what you want then you can simply code it like this
SELECT Lastname
, (CASE
WHEN AccountType = 'A1'
Then 'Good'
ELSE 'Not Good'
END) AS [Account Type]
, [Account type] AS [Actual type]
, count(*) as [NumCount]
FROM Tbl1
GROUP BY LastName, AccountType
Hope the above helps. Do let me know if this is what you were looking for.
Abhishek
"John" wrote:

> My current data result is something like this:
> LastName | Account Type | NumCount
> Miller | Good | 20
> Miller | Not Good | 5
> Jones | Not Good | 37
> Miller | Not Good | 9
> What I would like to see is the following:
> LastName | Account Type Actual Type | NumCount
> Miller | Good | A1 |
> 20
> Miller | Not Good | A2 |
5
> Jones | Not Good | A3 |
37
> Miller | Not Good | A4 |
> 9
> In the first example I am grouping by LastName, [Account Type]
> In the second example I need to Group by the same and addition to the Actu
al
> Account Type.
> The problem here though is that the column "AccountType" needs to be used
> twice and I don't know how to handle this. Unfortunately I can not use a
> unique alias for each one that can be Grouped.
> John.
> "Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
> message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
>
>