• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Need Help with SQL Statment

I have the following sql statement that gives me the information I need.   The problem is the data comes back in this format.  I need the statement to great one record if the laName and locationid are the same.  We have people working at location that may manage many properties.  Sales are tracked separately, but I need them combined.  If there is a way to do it.

HP 15  Dayhoff  0      0      0      0      0      0.0      MD
HG 22  DeLauter 0      0      0      0      0      0.0 MD
B2 18  Johnson 0      0      0      0      0      0.0
BR18  Johnson 0      0      0      0      0      0.0      MD
BR 18 Mandaglio 6      3      0      3      6      6.0      MD
HP 15 Mandaglio 0      0      0      0      0      0.0      MD
B2 18 Mandaglio 15      4      0      1      5      15.0      MD
B1 18 Mandaglio 13      5      0      2      5      13.0      MD
WA18  Mandaglio 18      9      1      0      2      18.0      MD
WT18  Mandaglio 23      9      1      1      5      23.0      MD



Select leasesummary1.propertyid,locationlist.locationid,leasesummary1.laname,leasesummary1.totalshown,leasesummary1.returnvisits,leasesummary1.cancels,leasesummary1.denials,leasesummary1.totalclosed,leasesummary1.nettraffic,locationlist.state from leaseSummary1 inner join locationlist on leasesummary1.propertyid = locationlist.code WHERE historylevel = 1 and userid = 'supervisor0' and ytdflag = 'Y'ORDER BY  LANAME

Open in new window

0
mgmhicks
Asked:
mgmhicks
  • 6
  • 3
  • 3
  • +1
1 Solution
 
StrongBad_RulesCommented:
use
Group By leasesummary1.laname
at the end of the statement.
0
 
mgmhicksAuthor Commented:
Here is what I get.  I will resend the sql statment that I used.  Thanks for helping

Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.PropertyId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'locationlist.LocationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.TotalShown' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.ReturnVisits' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.Cancels' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.Denials' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.TotalClosed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'leaseSummary1.NetTraffic' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'locationlist.State' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Select leasesummary1.propertyid,locationlist.locationid,leasesummary1.laname,leasesummary1.totalshown,leasesummary1.returnvisits,leasesummary1.cancels,leasesummary1.denials,leasesummary1.totalclosed,leasesummary1.nettraffic,locationlist.state from leaseSummary1 
inner join locationlist on leasesummary1.propertyid = locationlist.code 
WHERE historylevel = 1 and userid = 'supervisor0' and ytdflag = 'Y' group by leasesummary1.laname

Open in new window

0
 
StrongBad_RulesCommented:
That is because you need an aggregate function for the other columns.  When you select Mandagalio for instance, which instance of the other columns are you supposed to use or are you supposed to use some combination of the instances?  The database has no idea.  
Check this out:
http://www.w3schools.com/sql/sql_groupby.asp
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mgmhicksAuthor Commented:
Can i create a cursor in the procedure and then group the cursor.  Like create cursor with existing sql statement, and then create results as a select statement from the cursor?
0
 
UnifiedISCommented:
Select leasesummary1.propertyid,locationlist.locationid,leasesummary1.laname,SUM(leasesummary1.totalshown),
SUM(leasesummary1.returnvisits), SUM(leasesummary1.cancels), SUM(leasesummary1.denials),
SUM(leasesummary1.totalclosed),
SUM(leasesummary1.nettraffic),
locationlist.state from leaseSummary1 inner join locationlist on leasesummary1.propertyid = locationlist.code WHERE historylevel = 1 and userid = 'supervisor0' and ytdflag = 'Y'
GROUP BY leasesummary1.propertyid,locationlist.locationid,leasesummary1.laname, locationlist.state
ORDER BY  LANAME
0
 
mgmhicksAuthor Commented:
UnifiedIS I tried your script and received the following.  I need it to combine  the locationid and the laname if they are the same.   I have 5 properties lets say at 1 location.  So if the person is at that location she could have a record for 5 properties.  I want to combine them because they are all at the same location.

B2 18  Cain      0      0      0      0      0      0.0      MD
BR 18  Cain      0      0      0      0      0      0.0      MD
WT 18  Cain      0      0      0      0      0      0.0      MD
HP 15  Dayhoff0      0      0      0      0      0.0      MD
HG 22 DeLauter0      0      0      0      0      0.0      MD
B2 18 Johnson 0      0      0      0      0      0.0      MD
BR18  Johnson0      0      0      0      0      0.0      MD
HP 15 Mandaglio0      0      0      0      0      0.0      MD
B1 18 Mandaglio 13      5      0      2      5      13.0      MD
B2 18 Mandaglio15      4      0      1      5      15.0      MD
BR18 Mandaglio 6      3      0      3      6      6.0      MD
0
 
StrongBad_RulesCommented:
So what do you want your resulting data to look like?
0
 
SharathData EngineerCommented:
Are you looking for query like this?
Select t1.propertyid,
       t2.locationid,
       t1.laname,
       t1.totalshown,
       t1.returnvisits,
       t1.cancels,
       t1.denials,
       t1.totalclosed,
       t1.nettraffic,
       locationlist.state 
  from locationlist t1
 inner join locationlist t2
    on t1.propertyid = locationlist.code 
 inner join (select t4.locationid,t3.laname,min(t3.propertyid) propertyid
               from locationlist t3
               join locationlist t4 on t3.propertyid = t4.code
              group by t4.locationid,t3.laname) t5
    on t1.lname = t5.lname and t2.locationid = t5.locationid and t1.propertyid = t5.propertyid
 where historylevel = 1 
   and userid = 'supervisor0' 
   and ytdflag = 'Y'
 order by laname
 

Open in new window

0
 
SharathData EngineerCommented:
check this.

Select t1.propertyid,
       t2.locationid,
       t1.laname,
       t1.totalshown,
       t1.returnvisits,
       t1.cancels,
       t1.denials,
       t1.totalclosed,
       t1.nettraffic,
       locationlist.state 
  from locationlist t1
 inner join locationlist t2
    on t1.propertyid = t2.code 
 inner join (select t4.locationid,t3.laname,min(t3.propertyid) propertyid
               from locationlist t3
               join locationlist t4 on t3.propertyid = t4.code
              group by t4.locationid,t3.laname) t5
    on t1.lname = t5.lname and t2.locationid = t5.locationid and t1.propertyid = t5.propertyid
 where historylevel = 1 
   and userid = 'supervisor0' 
   and ytdflag = 'Y'
 order by laname
 

Open in new window

0
 
mgmhicksAuthor Commented:
Ended up changing yours a little cause propertyid, laname comes from leasesummary1 table.  But when I ran it, received no results.

Select t1.propertyid,
       t2.locationid,
       t1.laname,
       t1.totalshown,
       t1.returnvisits,
       t1.cancels,
       t1.denials,
       t1.totalclosed,
       t1.nettraffic,
       t2.state 
  from leasesummary1 t1
 inner join locationlist t2
    on t1.propertyid = t2.code 
 inner join (select t4.locationid,t3.laname,min(t3.propertyid) propertyid
               from leasesummary1 t3
               join locationlist t4 on t3.propertyid = t4.code
              group by t4.locationid,t3.laname) t5
    on t1.laname = t5.laname and t2.locationid = t5.locationid and t1.propertyid = t5.propertyid
 where historylevel = 1 
   and userid = 'supervisor0' 
   and ytdflag = 'Y'
ORDER BY t1.laname
 
 

Open in new window

0
 
UnifiedISCommented:
If you need to group by name and location, then propertyid and state should be removed.  This will total your value columns and group by lastname and location

Select locationlist.locationid,leasesummary1.laname,SUM(leasesummary1.totalshown),
SUM(leasesummary1.returnvisits), SUM(leasesummary1.cancels), SUM(leasesummary1.denials),
SUM(leasesummary1.totalclosed),
SUM(leasesummary1.nettraffic)
from leaseSummary1 inner join locationlist on leasesummary1.propertyid = locationlist.code WHERE historylevel = 1 and userid = 'supervisor0' and ytdflag = 'Y'
GROUP BY locationlist.locationid,leasesummary1.laname
ORDER BY  LANAME
0
 
mgmhicksAuthor Commented:
Sorry unifiedIS that brought up no results.
0
 
mgmhicksAuthor Commented:
UnifiedIS i finally did get results by no fault of yours  However the results are still not right.  Here are the results
1 Daneille Bigler      13      0      1      1      6      13.0
1 Danielle Bigler      22      0      2      0      13      22.0
1 Hannah Rethi      1      0      0      0      1      1.0
1 Jillian James      12      0      2      0      9      12.0
1 Tonya Cuevas      6      0      0      0      3      6.0
1 Tonya Cuvees      3      0      0      0      0      3.0

If the LocationID (1) and the name (Daneille Bigler) the results should be combined.  Looks like I have to do it in code, rather then the sql script?  Your thoughts.
0
 
SharathData EngineerCommented:
what about this?
Select t1.propertyid,
       t2.locationid,
       t1.laname,
       t1.totalshown,
       t1.returnvisits,
       t1.cancels,
       t1.denials,
       t1.totalclosed,
       t1.nettraffic,
       t2.state 
  from leasesummary1 t1
 inner join locationlist t2
    on t1.propertyid = t2.code 
 inner join (select t4.locationid,t3.laname,min(t3.propertyid) propertyid
               from leasesummary1 t3
               join locationlist t4 on t3.propertyid = t4.code
              where historylevel = 1 and userid = 'supervisor0' and ytdflag = 'Y'
              group by t4.locationid,t3.laname) t5
    on t1.laname = t5.laname and t2.locationid = t5.locationid and t1.propertyid = t5.propertyid
 where historylevel = 1 
   and userid = 'supervisor0' 
   and ytdflag = 'Y'
ORDER BY t1.laname

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now