group by Left(fieldname) 1

[abenitez77]

I want to group by Left(fieldname) in my query and it is not working for me, how can i do this? I want to see the full name and not just left 4 of the sourcedatabase fields when i return the records.

SELECT max([SourceServer]) AS SourceServer
,Left([SourceDatabase],4) as SD
,[Processed]
FROM [DevClaimsFull].[dbo].[ApClientVndYrLineage]
GROUP BY Left([SourceDatabase],4), Processed
ORDER BY SD

 

[r937]

could you show a few lines of actual data from the table, and then explain what you mean using that data...

right now it's kind of hard to tell what you want, LEFT(fieldname) or the full name (whatever that means)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[abenitez77]

SourceServer SD Processed
server1 ACE_ TRUE
server2 AHOL TRUE
server1 ALBE TRUE
server5 ALBE TRUE
server9 BARN FALSE
server8 Bash True
server9 Bash True

this is the data it returns. SD, has the full name because I am doing a left(sourcedatabase,4) I only get the left 4 of the full name. ie ALBE = ALBERTSONS. I want to return ALBERTSONS AND NOT ALBE.

 

[RiverGuy]

Why would you want to group on only the left four characters if you want to return the full name?

 

[r937]

try this --

SELECT g.SourceServer
     , t.SourceDatabase
     , g.Processed
  FROM ( SELECT MAX(SourceServer) AS SourceServer      
              , LEFT(SourceDatabase,4) AS SD      
              , Processed   
           FROM DevClaimsFull.dbo.ApClientVndYrLineage   
         GROUP 
             BY LEFT(SourceDatabase,4)
              , Processed     
       ) AS g
INNER
  JOIN DevClaimsFull.dbo.ApClientVndYrLineage AS t
    ON LEFT(t.SourceDatabase,4) = g.SD
ORDER 
    BY g.SD

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[abenitez77]

r937, That worked!.... Thanks!

Riverguy, The reason I want to group by left 4 and see the whole field displayed is because there are variations in the name in that column. i.e. ACE_2009_Tep, ACE_2010_May, ACE_2010_June.

Now, there is another view I would like to see this data.

How about if I wanted to group by the content in the field?
ie
sourcedatabase
ACE_2010_June_Temp_W
ACE_2009_May_F
ACE_HARDWARE_AP_2007_AP_W
ACE_HARDWARE_2006_F

Say I wanted to group by ACE (the name of the cient). This is always what is before the first occurance of "_"(underscore). Then the Year, which could be anywhere after the first underscore. How can i accomplish this?

 

[abenitez77]

r937, Actually your close but in checking the results again, I see that it is not rolling up sourcedatabase. See the results below. It is not grouping by left 4 on sourcedatabase.

SourceServer SourceDatabase Processed
USATL02PRSQ11 AAFES_2009_TEMP_W Error
USATL02PRSQ11 AAFES_2009_TEMP_W True
USATL02PRSQ11 AAFES_2009_TEMP_W Error
USATL02PRSQ11 AAFES_2009_TEMP_W True
USATL02PRSQ11 AAFES_2009_TEMP_W Error
USATL02PRSQ11 AAFES_2009_TEMP_W True
USATL02PRSQ11 AAFES_2010_MAY_F Error
USATL02PRSQ11 AAFES_2010_MAY_F True
USATL02PRSQ11 AAFES_2010_MAY_F Error
USATL02PRSQ11 AAFES_2010_MAY_F True
USATL02PRSQ13 ACE_HARDWARE_CORP_AP_2007_AP_W True
USATL02PRSQ13 ACE_HARDWARE_CORP_AP_2007_AP_W True
USATL02PRSQ13 ACE_HARDWARE_CORP_AP_2007_AP_W True

 

[r937]

It is not grouping by left 4 on sourcedatabase

oh yes it is ;-)


however, since you want to see the detailed SourceDatabase values that match the aggregated 4-character values, this is in fact an "un-aggregation"

here's another way to think about it...

suppose you wanted to have a query that aggregated the populations of all the towns in each state, so that for each state, you get one aggregate total

but then you say you want to see the individual towns as well

so when this query shows its results, you get to see the state total next to each town, right?

you can't say that it isn't rolling up the towns into states, because it is, and yet there is a row for every town

it's the same here with your query, except instead of a SUM total, you're seeing the MAX value



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[abenitez77]

So, I guess what i want to do is group by part of the name. If I wanted to group by Sourcedatabase and do it by client name and year (this is what makes up the sourcedatabase field). It would be the beginning of the string until you get to the first occorence of an underscore ('_'). Then the year could be in any part of the string. Most of the times it is after the client name. So I would be groupying by those 2 things and return the results with the full sourcedatabase name for the records returned.

Is that possible and how can i do this?

 

[r937]

yes, it is possible, but the results will be just as disappointing, you will have un-aggregated results again

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon