Excel Data Query from SQL

[krissz]

To keep it simple, I have a large SQL table with 3 fields: Date, Account & Value.
I want the sum of values for each account for a defined time period.

This is simple using SUM() & GROUP BY.

However, there may not be any activities for some accounts during the period, in which case I want 0 returned. What happens is that such accounts are not returned.

I have searched & tried many solutions to no avail - ISNULL, COALESCE, CASE, etc. The simplified code is:

SELECT Date, Account, SUM(Value)
FROM Database
WHERE date BETWEEN 'Date1' AND 'Date2'
GROUP BY Account
ORDER BY Account

The Account field is integer numeric, if that helps
I suspect that the WHERE condition limits the SUM function so that there are no NULL rows generated. How do I get round this ?
The actual database is large so I would not want import it in total.

 

[Hermanito]

You were on the right track with COALESCE, but you need a left join to get the results you need.
The query you show cannot work, because the Date field is not included in the group by clause... I assume you simplified your actual query but forgot to test

I constructed a query that does what you need, but only returns the Account and the sum... if you need the Date as well, adapt as needed:

SELECT DISTINCT A.Account, COALESCE(R.SumValue,0) FROM Accounts A LEFT JOIN
(
SELECT Account, SUM(Value)AS SumValue
FROM Accounts
WHERE date BETWEEN 'Date1' AND 'Date2'
GROUP BY Account
) R
ON A.Account=R.Account

The first select makes sure I get a result record for each existing account, the left join on the subselect makes sure I get the calculated sum if it exists, and the coalesce puts 0 as sum, if the account did not exist in the subselect query.

good luck,
H

 

[krissz]

Hermanito
Thank you very much - it works. Also thanks for the explanation.

You were right - I had very much simplified the query, and in the process incorrectly added 'date' to the select statement.
I have added to other necessary columns without any problems

As a novice, I have some more queries:
1. I am trying to add a name for the 'Account' column, but wherever I put the AS statement I get an error
Allocating names to the other fields works in the first SELECT statement
2. I presume that A and R are just markers and can be any letter or string
3. Is there something like a wild card in SQL which - when allocated to a parameter - means 'Any Value' ?

 

[krissz]

Hermanito
As I said, it works perfectly when I try it on the server.

When I copy the complete query into MS Query to get it ito excel, I get an error message.
"No column name was specified for Column 2 of R. Invalid column name "SumValue""

What is wrong, or is there another way to import

 

[Hermanito]

1) not sure why that doesn't work, it should... can you show the full query where it doesn't work?
2) A and R can be anything indeed, they are what's called aliases, when using different tables, they are not needed (but can come in handy to shorten the typing), but when joining a table to itself, you need a way to distinguish the two, hence the aliases.
3) not sure what you mean by this question... please explain further what you want to do

MS Query uses a non-standard dialect of SQL, I think it does not even support subselects as in my query, not sure about the alias for a subselect. You might try with 'AS R' instead of just R after the closing parenthesis.

Could you elaborate on what you are trying to achieve, and on which platform? Excel, Access, MS Query, SQL Server? They all have their particularities when it comes to SQL and only SQL Server uses ISO standard SQL (AFAIK).

 

[krissz]

The Company is centralising all data onto an MS SQL server. The old systems used Access, Btrieve, etc; nightly runs exported data into CSV files which provided the input to Excel spreadsheets - some simpl while others used VB. Always Day+1. I am now trying to learn MSSQL by generating the necessary queries to bring SQL data into the spreadsheets, preferably in real-time in many cases. In order to make the queries as generic as possible, some of the parameters are picked from cells in the spreadsheet.

When I use MS Query, I find the following works:
QuertDate BETWEEN [StartDate] and [EndDate]
This seems to be a specific Query Syntax.

In my (much simplified) sample code, which was interrogating Sage200 tables, Date1 and Date2 were parameters that were to be picked up from cells in the Excel spreadsheet.

I tried using Query to import your code, which failed. Tried AS R, but no change. I then tried an OLE DB Query with your SQL code. This time I could not find a way of passing the parameters through. Then tried to save a parameterised query, but found that I do not have the rights to save this. (Will progress this on Monday)

Next, I declared local variables, but again failed to find a way of inputing data into these variables.

I know that I can fix the Parameters in each query, but then I do not have a generic - I have to manually change the query every time.

I hope this helps. The present system is automated and only requires occasional tweeks. I hope to setup a similar approach with the SQL server, which is why I want generic code. Sorting out the MIS systems etc is only a small part of my job, but has become almost full time.

Thank you for your help

 

[krissz]

1) not sure why that doesn't work, it should... can you show the full query where it doesn't work?

This is the final code which works

SELECT    DISTINCT A.AccountNumber,
         AccountName AS 'Name', 
         COALESCE(R.SumAc,0)

FROM qryNLSLPLPostedTrans A LEFT JOIN
(
SELECT    AccountNumber , 
        SUM(GoodsValueInBaseCurrency) AS 'SumAc'
FROM    qryNLSLPLPostedTrans
WHERE    TransactionDate BETWEEN  '2012-07-01' AND '2013-06-30'

GROUP BY AccountNumber
) R
ON        A.AccountNumber=R.AccountNumber
ORDER BY    AccountNumber, AccountName

I am trying to add a column name for AccountNumber, but do not know where to put it. It does not work after A.AccountNumber. Also, the Column for SumAc says 'No Column Name'
This is possibly more academic (my learning process) as I can import without the headers.

Thanks again

 

[SydneyGeek]

Try changing

                  COALESCE(R.SumAc,0)

to

                  COALESCE(R.SumAc,0) AS 'AccountSum'

Denis

 

[krissz]

Denis
Thank you. It's so obvious that I am emabrassed at having had to ask.
Kris