Solved

sql synax

Posted on 2012-04-04
30
346 Views
Last Modified: 2012-04-11
I have attached excel and please assume it is just a database table in sql server 2010.
I know how to write the sql syntax to group them to show as attachment except the one showing 0 premium.

When the premium is zero (AgencyId sis 1003 in this case), this line is always disappeared.
I just want to have result set exactly like attachment.

I did try to use left join but it still does not work. Please help
Sample.xlsx
0
Comment
Question by:Webboy2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 5
  • +3
30 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 37808470
Left Join to what?

Please reveal your SQL that is not working
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 37808567
Are these columns in 2 different tables ???
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808607
Did you try something like this:
Select t2.Premium, t1.AgencyId
From AgencyTable t1
LEFT OUTER JOIN PremimumTable t2 ON t1.AgencyId = t2.AgencyId
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37808648
I am assuming that there is no reference to the AgencyID in the Premium table for the join to link on to
you could try to set the premium field to an explicit value for cases where it is null...

Select (
 CASE
          WHEN Premium IS NULL THEN '0'
          ELSE Premium
     END), as 'Premium',
     AgencyID
from ...
0
 

Author Comment

by:Webboy2008
ID: 37808742
I have one associated table called AgencyName(AgencyName is  the column name) and have key agencyId

I need to get working sql syntax if possible. thank
0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37808759
Can you include the SQL you are currently using for this?
0
 

Author Comment

by:Webboy2008
ID: 37808890
I attached access database for your review. All i want is to get all of agencyId regardless of the premium from 0 to any numbers.
Database1.accdb
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37808940
I'm sorry, I don't understand your issue. I have downloaded your database and there is nothing inherent that suppresses agencyId's with zero premium.  Whatever is causing that problem is in your SQL, which for some reason you don't want to reveal.

Perhaps we can approach it this way.  Given the tables in your example database, show us the results in table form that you want to see.
0
 

Author Comment

by:Webboy2008
ID: 37808951
Please check my desire result from my original attached. Sample.xlsx from my question
0
 

Author Comment

by:Webboy2008
ID: 37808969
Check this attached. I added query there. The query is working but what I really want to see is Agency E (AgencyId=5). even it does not exist in the transaction table.
Database1.accdb
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808974
Try this query.  I just ran it against your DB and Agency E shows up.

SELECT Agency.AgencyId, Agency.AgencyName, Sum(Transactions.Premium) AS Premium
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId, Agency.AgencyName;
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808988
Or here is your database back with your query corrected to return all 5 Agencies.
Database1.accdb
0
 

Author Comment

by:Webboy2008
ID: 37809013
i added the query in the sql server to test, and it does not show the agencies (the last one without any trans in transactions table).
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809025
No problem:

SELECT Sum(nz(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;
0
 

Author Comment

by:Webboy2008
ID: 37809034
my bad. please use this attached db. the agencyId is actually text, not number, and now the query does not show the last agency...
Database1.accdb
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809036
In SQL Server, the equivalent is this:

SELECT Sum(ISNULL(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37809039
Did it work in the Access Database? It did for me when I ran it.
What is your exact query you are running in the real SQL Server?  You must have some syntax incorrect.
Sample-Query-Results.png
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809050
The query is the same regardless of whether AgencyID is text or number.   The important part to return the Agencies not represented in the Transaction table is to us an Outer (Left in this case) join.
0
 

Author Comment

by:Webboy2008
ID: 37809057
dqmq: It is different.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37809062
Are you not getting the row back or is the value for SumOfPremium still null?

If the later, try this
SELECT ISNULL(Sum(Transactions.Premium),0) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809065
I am saying that changing the datatype of AgencyID, does not change my answer.  It works either way.
0
 

Author Comment

by:Webboy2008
ID: 37809071
I got alert message when i try to run your query.
-wrong number of argument used with function in query expression isnull(...),0
0
 

Author Comment

by:Webboy2008
ID: 37809078
dqmq: Just download the latest access file and you will know what i mean.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809440
I did.  And the datatype does not affect the query, nor my answer.  The reason you got the "wrong number of arguments" message  is because you ran the SQL server query on Access.  

Here's the the Access query:

SELECT Sum(nz(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

Open in new window


Here's the equivalent SQL Server query:

SELECT Sum(isnull(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

Open in new window

0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 37810244
Hi update your query as follows

SELECT Sum(Transactions.Premium) AS SumOfPremium, Agency.AgencyId
FROM Agency RIGHT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;


See Output Below

Output as you want !!!
0
 

Author Comment

by:Webboy2008
ID: 37813393
My question is to show up agencyid 5, even the transaction table does not have agencyid 5 there.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37814030
The SQL I've given you returns exactly those results.  Please try it.  If the results are not satisfactory, then please explain.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814550
The SQL I've given you returns exactly those results.
I am still confused as to what SQL dialect the author needs.  Is it T-SQL or the SQL dialect used by MS Access as you propose in your solution?  
When the author stated:
-wrong number of argument used with function in query expression isnull(...),0
Whose solution were they referring to?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37814672
Agreed, confusion reigns.  The author delivers an Access sandbox to us, but then claims to have loaded the query into SQL Server for testing.  What's an expert to do? I offered equivalent solutions in both dialects and also noted that the author's error was caused by using the wrong one.  It's frustrating because on the scale of difficulty this problem barely makes it on the chart and here we are 29 comments later.

This "smells" like one of those cases where for the sake of simplification or privacy we are given a contrived situation and our solutions are not being applied correctly to the real one.  I dunno.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814779
I could not agree more.  It is a perfectly trivial problem that has not been explained correctly.  If the author really wanted an answer they should have done the following:
1. Provide a script to create the table(s).
2. A script to populate the data.
3. The output required using that data.
That is it.  It is not difficult.  You do not even have to explain it in English.

What they did was provide an Excel spreadsheet with the following data and with the clarification that we should "assume it is just a database table in sql server 2010" (The fact that "sql server 2010" never existed should have been a warning):
Premium      AgencyId
5000      1000
2000      1001
10000      1002
0      1003

Then we are told that there is "one associated table called AgencyName(AgencyName is  the column name) and have key agencyId"  Whatever that means...

They then attach an MS Access database.

Then we get another MS Access database and "I added query there. The query is working but what I really want to see is Agency E (AgencyId=5). even it does not exist in the transaction table."

And then a third one with the note that "the agencyId is actually text, not number, and now the query does not show the last agency..."

Then we get the cryptic "dqmq: It is different."  What is different?

And then "I got alert message when i try to run your query."  in response to what?

And so and so on...
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question