Question Locked

Sub-query gives no data and this results in an error

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have various sub-queries which are used in a query which summarises related information. One of the sub-queries is designed to find out how many referrals were received in a particular month in a particular locality. There are 9 different localities. The user enters the parameters: [locality], [start date] and [end date]. However, in some localities there may be no referrals at all in a particular month. If this is the case, the query returns no data. The query that this feeds into then also returns no data at all, in any of its colums and its associated report gives an error message.

How can I get my first sub-query to return data for all 9 localities, with 0 if there were no referrals for the period?

I am not an IT professional but have responsibility for establishing and maintaining 11 similar databases (in addition to the rest of my work)!

I would be delighted if anyone could help me and thank you in advance.

5 replies

by tony hopkinson

This one uses a union

Select Locality,Sum(Referals) as Referals
From
(your current query
UNION
(Select Locality,0 From Localities Where Locality = ...
) dummyname
Group By Locality

The trick is to union you current result with a record of locality and zero, then sum them so you get zero or a number of referals.
It could even be

UNION
Select 'My Little Town', 0

dummyname isn't used in this case but is required by most sql parsers I've used.

Obviously I don't know your db schema but hopefully you'll get the idea

PS put the extra line second that way the field names in the result will match the real column names.
SQL only cares that they are the same type.

by claire.milligan

Hi Tony

Thanks very much for your really prompt reply. I've been trying to follow your advice and feel that I am nearly there. This is what I have:

SELECT [Pre 5 Learning Disabilities].Town,Count ([Pre 5 Learning Disabilities].[Date Received]) AS [CountOfDate Received]
FROM [Pre 5 Learning Disabilities]
WHERE ((([Pre 5 Learning Disabilities].[Date Received]) Between [start date] And [end date]))
GROUP BY [Pre 5 Learning Disabilities].Town
UNION
SELECT [Localities].Town,0 FROM Localities
GROUP BY [Localities].Town;

The name of my original table is Pre 5 Learning Difficulties, and I made a new table to union with called Localities. The result I get when I run the query is:

Town CountOfDate Received

A-town 0
A-town 2
B-town 0
B-town 5
C-town 0
D-town 0
D-town 3 etc

i.e. the results of the query on each table is being shown separately rather than being added together (town C does not return data from the query on the original table).

What am I missing?

Thanks again.

by tony hopkinson

Select Town,Sum(CountOfDate) as CountOfDate
From
(
SELECT [Pre 5 Learning Disabilities].Town,Count ([Pre 5 Learning Disabilities].[Date Received]) AS [CountOfDate Received]
FROM [Pre 5 Learning Disabilities]
WHERE ((([Pre 5 Learning Disabilities].[Date Received]) Between [start date] And [end date]))
GROUP BY [Pre 5 Learning Disabilities].Town
UNION
SELECT [Localities].Town,0 FROM Localities
GROUP BY [Localities].Town
)
Group By Town

Should Give you

A-town 2
B-town 5
C-town 0
D-town 3 etc

by claire.milligan

Thanks Tony for all your help on this. I have finally achieved the result I was look for!

This discussion is closed to new replies.