Need Guidance On Query
-
I'm the first to admit that I'm a novice at SQL and I have a need to generate a query that has me completely stumped. For instance in the following I need to find out if there is an avatarid/episode combination that does not have a matching status of DISCHARGE. In most cases, there is a pair of rows for each combination; one ADD and one DISCHARGE. There is an additional column of ssn that is part of the where clause. A colleague had me try the following but it errors with 147, An aggregate may not appear...
SELECT avatarid, episode FROM clients WHERE ssn='123-12-1234' and count(CASE WHEN status='DISCHARGE' THEN 1 else 0 END)=0 group by avatarid
I'm guessing that this is simple for someone that does this regularly but I'm stumped. In the image below I would want the query to return 662 & 2. If there was a DISCHARGE for episode 2 the query would return nothing. Can someone give me some guidance please?
-
A few things:
1) You said this was per avatarid/episode combination, so you would need to group by both columns, not just avatarid.
2) To filter based on aggregates, you should use the HAVING clause.
3) COUNT will not work as the aggregate function, since it just counts values. An avatarid/episode combination that has a single 'ADD' row will still return 1 for the count. You would want to use MAX instead.
Something like this:
SELECT avatarid,episode
FROM clients
WHERE ssn='123-12-1234'
GROUP BY avatarid,
episode
HAVING MAX(CASE WHEN status='DISCHARGE' THEN 1 else 0 END)=0
Cheers!
-
COUNT will work as an aggregate if you return a NULL in the proper cases. COUNT() may not perform the best, but it will work.
COUNT(CASE WHEN status='Discharge' THEN 1 ELSE NULL END)
/*
or since the NULL is automatically returned if there is no ELSE clause
and a record doesn't meet any of the other criteria
*/
COUNT(CASE WHEN status='Discharge' THEN 1 END
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
If you ask me, the adequate option for this is to use EXCEPT. I have to change the name of a column due to the internet filter configuration in my office. It shouldn't be a big deal to figure out which.
I'm also including sample data the way it should be posted, so you can include it on your future posts.
CREATE TABLE clients(
avatarid int,
episodeint,
status varchar(10),
thenumber char(11)
);
INSERT INTO clients
VALUES
(662, 1, 'ADD', '123-12-1234'),
(662, 2, 'ADD', '123-12-1234'),
(662, 1, 'DISCHARGE', '123-12-1234');
SELECT avatarid, episode
FROM clients
WHERE status = 'ADD'
AND thenumber = '123-12-1234'
EXCEPT
SELECT avatarid, episode
FROM clients
WHERE status = 'DISCHARGE'
AND thenumber = '123-12-1234';
GO
DROP TABLE clients;
EDIT: If you want to know which values have the pair of rows, just change the EXCEPT for an INTERSECT.
-
drew.allen (8/22/2016)
COUNT will work as an aggregate if you return a NULL in the proper cases. COUNT() may not perform the best, but it will work.COUNT(CASE WHEN status='Discharge' THEN 1 ELSE NULL END)
/*
or since the NULL is automatically returned if there is no ELSE clause
and a record doesn't meet any of the other criteria
*/
COUNT(CASE WHEN status='Discharge' THEN 1 END
Drew
Yes, it will clearly work with the necessary changes to the rest of the query.
I probably should have pointed out that if you change the CASE expression, COUNT could work.
Of course, with changes to the CASE expression and HAVING clause, then pretty much any aggregate function could work, even VARP or VAR. 🙂
Cheers!
-
Thanks all for the replys and options. I really appreciate it and as Luis' tag line suggests, I'll test and make sure I understand how it works before applying. This is a great forum!
-
Jacob Wilkins (8/22/2016)
drew.allen (8/22/2016)
COUNT will work as an aggregate if you return a NULL in the proper cases. COUNT() may not perform the best, but it will work.COUNT(CASE WHEN status='Discharge' THEN 1 ELSE NULL END)
/*
or since the NULL is automatically returned if there is no ELSE clause
and a record doesn't meet any of the other criteria
*/
COUNT(CASE WHEN status='Discharge' THEN 1 END
Drew
Yes, it will clearly work with the necessary changes to the rest of the query.
I probably should have pointed out that if you change the CASE expression, COUNT could work.
Of course, with changes to the CASE expression and HAVING clause, then pretty much any aggregate function could work, even VARP or VAR. 🙂
Cheers!
What I'm saying is that COUNT() makes sense to the OP, because that's what he used. I think it's much more encouraging to tell the OP that he was on the right track with COUNT() and to show him how to make that work and then show other similar methods that also work and may be more efficient. Telling him that COUNT() doesn't work (when it obviously does) makes the OP distrust his intuitions about how to approach the problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Depending on what your statuses are, you may not even need a case expression. If your only statuses are ADD and DISCHARGE, then you can just use MAX(status) < 'DISCHARGE'.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (8/22/2016)
Jacob Wilkins (8/22/2016)
drew.allen (8/22/2016)
COUNT will work as an aggregate if you return a NULL in the proper cases. COUNT() may not perform the best, but it will work.COUNT(CASE WHEN status='Discharge' THEN 1 ELSE NULL END)
/*
or since the NULL is automatically returned if there is no ELSE clause
and a record doesn't meet any of the other criteria
*/
COUNT(CASE WHEN status='Discharge' THEN 1 END
Drew
Yes, it will clearly work with the necessary changes to the rest of the query.
I probably should have pointed out that if you change the CASE expression, COUNT could work.
Of course, with changes to the CASE expression and HAVING clause, then pretty much any aggregate function could work, even VARP or VAR. 🙂
Cheers!
What I'm saying is that COUNT() makes sense to the OP, because that's what he used. I think it's much more encouraging to tell the OP that he was on the right track with COUNT() and to show him how to make that work and then show other similar methods that also work and may be more efficient. Telling him that COUNT() doesn't work (when it obviously does) makes the OP distrust his intuitions about how to approach the problem.
Drew
I understand your point, and that's why I said that I probably should have mentioned that COUNT would work if the CASE expression were changed. We just each latched onto a different part of the original attempt.
I focused on the CASE expression used, and figured since using that made sense to the OP, I'd suggest the aggregate function that worked with it. You focused on the aggregate function used, and suggested the CASE expression that worked with it.
Either way, all the information's out there now. 🙂
Cheers!
-
This just another possible way :
Select lt.*
FROM
(Select avatarID, episode From #clients where [status]='add') as lt
Left join
(Select avatarID, episode From #clients where [status]='discharge') as rt
ONlt.avatarID = rt.avatarID
and lt.episode=rt.episode
WHERE rt.avatarID is NULL and rt.episode IS NULL
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply