March 10, 2015 at 2:42 pm
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.
There are two tables I am using for this query.
Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
Table 2
id Value
1 10.00
1 10.00
3 5.00
I want to display the following:
Name Number Value
Coupon1 2 $20.00
Coupon2 0 $0.00
Coupon3 1 $5.00
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
March 10, 2015 at 3:02 pm
use a LEFT JOIN
and use ISNULL(,0) for your sum and count
March 10, 2015 at 3:38 pm
Still only shows the two rows and not the one that has nothing.
Here is my code. Not sure if this is what you meant.
SELECT Table1.Name, ISNULL(COUNT(Table2.ID),0) AS CNum, ISNULL(SUM(Table2.Value),0) AS CValue FROM Table2 LEFT JOIN Table1 ON Table2.T1ID = Table1.ID GROUP BY Table1.Name
March 10, 2015 at 3:41 pm
sorry,
make table1 come after FROM and LEFT JOIN to Table2
March 10, 2015 at 3:45 pm
Yes, that did it.
Thank you very much.
March 10, 2015 at 5:32 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply