How to show result for count=0 in single query
-
Hello guys,
Using Northwind as example:
select count(1) as count from orders
where CustomerID = 'VINET' and EmployeeID = 10
group by customerid
There is no matched record, what I am hoping is I need a count = 0 as result in this case.
How do I do this? Thanks.
-
If you remove the GROUP BY clause you will get a count of 0. As there are no groups (customerIDs) that match the where clause no rows are returned.
SELECT count(1) as count FROM orders
WHERE CustomerID = 'VINET' and EmployeeID = 10
-
But I have to put the group by there. That's the key. I am just using the Northwind as an example.
I also have to accomplish this by using a SINGLE query for some reason.
-
Slightly more long-winded than the original, but it seems to work:
DECLARE @i int
select @i = count(*)
from orders
where CustomerID = 'VINET' and EmployeeID = 10
group by customerid
SELECT COALESCE(@i, 0) AS count
John
-
This can of course easily handled by using multiple queries in sp, but I have to embed the sql command in front end code, that is, I can't store it somewhere on server. So that's why I said I have to do this in ONE SINGLE SELECT QUERY.
-
Apologies. I started typing my reply before you added that requirement.
-
Why do you have to use the GROUP BY? I would understand the use if your query looked like:
SELECT col1, COUNT(col2)
FROM table
WHERE col1 = @var1
GROUP BY col1
It is very hard to help solve a problem that has not been clearly defined. If there is a security issue with the data make up some test data, post the DDL of the tables involved (with made up names if need be) and what you expect the output to be using your test data.
-
The purpose of this query is to generate feed for other method in front end. For some reason it always asks for, say, Jan-Dec data as data input in that method, if there are Jan-Mar data but Apr is missing, then it fails somehow. I was thinking it shouldn't be too difficult to handle this in sql instead of modifying other front end code. Group By is a not a must but the grouped value is a must.
-
select EmployeeID, [count]=case when count(1)>0 then count(1) else 0 end from orders
where CustomerID = 'VINET'
group by customerid, EmployeeID
This returns:
EmployeeID count
----------- -----------
2 2
3 1
5 1
6 1
(4 row(s) affected)
What the front end code is looking for is:
EmployeeID count
----------- -----------
1 0
2 2
3 1
4 0
5 1
6 1
-
You would have to have a lookup table of ALL of the values possible. You could create this on the fly in your SQL statement if it is necessary. Using a months of the year example:
CREATE
TABLE #months (monthID INT, monthName CHAR(3), monthValue INT)
INSERT
INTO #months VALUES (1, 'Jan', 1)
INSERT INTO #months VALUES (1, 'Jan', 2)
INSERT INTO #months VALUES (1, 'Jan', 3)
INSERT INTO #months VALUES (2, 'Feb', 1)
INSERT INTO #months VALUES (2, 'Feb', 2)
INSERT INTO #months VALUES (3, 'Mar', 1)
INSERT INTO #months VALUES (5, 'May', 1)
INSERT INTO #months VALUES (5, 'May', 2)
INSERT INTO #months VALUES (5, 'May', 3)
SELECT
flyMonthLookup.monthName, COUNT(monthValue)
FROM #months
RIGHT JOIN ( SELECT 'Jan' as monthName UNION ALL
SELECT 'Feb' as monthName UNION ALL
SELECT 'Mar' as monthName UNION ALL
SELECT 'Apr' as monthName UNION ALL
SELECT 'May' as monthName UNION ALL
SELECT 'Jun' as monthName UNION ALL
SELECT 'Jul' as monthName UNION ALL
SELECT 'Aug' as monthName UNION ALL
SELECT 'Sep' as monthName UNION ALL
SELECT 'Oct' as monthName UNION ALL
SELECT 'Nov' as monthName UNION ALL
SELECT 'Dec' as monthName)flyMonthLookup
ON #months.monthName = flyMonthLookup.monthName
GROUP BY flyMonthLookup.monthName, flyMonthLookup.monthName
DROP TABLE #months
Does this help with what you're after? Or have I missed the point totally?
-
Thank you Adrian, your query helps.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply