-1

I have the following results:

results without filtering

Gained through this query:

SELECT b.name, ISNULL(SUM(amount),0) AS 'Total', ISNULL(AVG(amount),0) AS 'Average'
FROM User_Transaction a
RIGHT OUTER JOIN Category b ON (a.category_id = b.category_id)
GROUP BY b.name

It contains the totals and averages of all financial transactions that users have logged, regardless of when the transaction was logged.

The User_Transaction table has a datestamp column that I would like to filter with. So I try the following:

SELECT b.name, ISNULL(SUM(amount),0) AS 'Total', ISNULL(AVG(amount),0) AS 'Average'
FROM User_Transaction a
RIGHT OUTER JOIN Category b ON (a.category_id = b.category_id)
WHERE 
 a.datestamp BETWEEN @date_from AND @date_to
GROUP BY b.name
ORDER BY b.name

This gives me:

results with failed filtering

The Other and Salary entries are missing, because no one has ever made a transaction with those names and therefore there is no date to filter on.

However, I would like to include these names, but still zero for their values. How can I do this?

  • Please suggest what is wrong with the question if it deserves a downvote. I’ve not asked many SQL questions before – August Williams Apr 13 at 19:06
  • The downvote isn't mine, but data as an image isn't well received with SQL questions. You're better off posting it as formatted text or as DDL and DML statements. – Larnu Apr 13 at 19:28
  • @Larnu I understand. Thank you for the advice - I will remember it for next time. – August Williams Apr 13 at 19:31
1

Try adding OR a.datestamp IS NULL to the where condition:

SELECT b.name, ISNULL(SUM(amount),0) AS 'Total', ISNULL(AVG(amount),0) AS 'Average'
FROM User_Transaction a
RIGHT OUTER JOIN Category b ON (a.category_id = b.category_id)
WHERE 
 a.datestamp BETWEEN @date_from AND @date_to OR a.datestamp IS NULL
GROUP BY b.name
ORDER BY b.name
2

Ok, so essentially you want to list ALL Categories, and include Total and Average where such data is available in the specified time period?

If that is your intent, then read on!

First of all I reformatted the query to make it easier to understand the structure. I renamed the table alias for User_Tranaction from "a" to "ut", and the alias for Category from "b" to "cat"

SELECT 
    cat.name, 
    ISNULL(SUM(amount),0) AS 'Total', 
    ISNULL(AVG(amount),0) AS 'Average'
FROM 
    User_Transaction AS ut
    RIGHT OUTER JOIN Category AS cat 
        ON ut.category_id = cat.category_id
WHERE 
    ut.datestamp BETWEEN @date_from AND @date_to
GROUP BY 
    cat.name
ORDER BY 
    cat.name

To achieve the desired effect of listing all the categories, and only matching user transactuions in the time period, i have flipped the table join around to use a LEFT OUTER JOIN from Category to User_Transaction. In addition i have moved the date filtering to the ON clause of the join to User_Transaction. This has the effect of pre-filtering the data in User_Transactions before the resulting data is linked to the Category data.

SELECT 
    cat.name, 
    ISNULL(SUM(amount),0) AS 'Total', 
    ISNULL(AVG(amount),0) AS 'Average'
FROM 
    Category AS cat
    LEFT OUTER JOIN User_Transaction AS ut
        ON cat.category_id = ut.category_id
        AND ut.datestamp BETWEEN @date_from AND @date_to
GROUP BY 
    cat.name
ORDER BY 
    cat.name

Hope that helps.

1

Here ya go

SELECT c.name, ISNULL(SUM(amount),0) AS 'Total', ISNULL(AVG(amount),0) AS 'Average'
FROM User_Transaction ut
RIGHT OUTER JOIN Category c ON (ut.category_id = c.category_id)
WHERE 
    (ut.datestamp BETWEEN @date_from AND @date_to) OR ut.datestamp IS NULL
GROUP BY c.name
ORDER BY c.name
0

I think that it'll work with an UNION : put firstly your request, and in the second part of the UNION select the entries where there are no dates to filter. It should work like that

  • I can see UNION working but the one problem with this could be efficiency. I believe the tables in the queries will be called twice. – August Williams Apr 13 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.