Grouping two similar column names but different data?

11 Nov 2005 3:42 PM

John

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.

11 Nov 2005 4:35 PM

Alejandro Mesa

John,

Can you post an example of the expected result?

AMB

Show quote

"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.
>
>
>

11 Nov 2005 5:19 PM

Abhishek Pandey

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

Show quote

"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.
>
>
>

11 Nov 2005 6:02 PM

John

"Abhishek Pandey" <AbhishekPan***@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:
>
>> 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.
>>
>>
>>

11 Nov 2005 7:17 PM

Abhishek Pandey

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

Show quote

"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 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" <AbhishekPan***@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:
> >
> >> 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.
> >>
> >>
> >>
>
>
>