We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Need Help with SQL Statment

mgmhicks
mgmhicks asked
on
Medium Priority
220 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

use
Group By leasesummary1.laname
at the end of the statement.

Author

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

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

Author

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?
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

Author

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
So what do you want your resulting data to look like?
SharathData Engineer
CERTIFIED EXPERT

Commented:
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

SharathData Engineer
CERTIFIED EXPERT

Commented:
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

Author

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

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

Author

Commented:
Sorry unifiedIS that brought up no results.

Author

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.
Data Engineer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.