Re: Counting in SQL Queries
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 16 Nov 2007 00:56:17 +0100
On Wed, 14 Nov 2007 10:17:03 -0800, Wayne wrote:
I am working on creating a Report using SRS but first have to get the correct
information out of my database with a query.
What I am attempting to do is create Honor Roll for one of my schools, and
the requirements for the Honor Roll is a gpa >3.0 and less than 3.99, and
they are only allowed to have one C of any type to be on the list.
I've got the majority of the query down but cannot get it to count
correctly for the number of C's that a student has. I thought I had it fixed
and it seems like it weeds out some names but leaves others.
Here is what I have so far in trying to get it to work.
Thanks in Advance for any suggesstions
Hi Wayne,
Your query is a bit too long and complicated for me to understand
everything you do, so I'll give some generic guidelines for you to work
into your solution. My example code search for orders with exactly one
line for a product that starts with 'D'
There are two ways to test for "exactly 1 of XXXX". The easiest to
understand is with a subquery with COUNT:
SELECT o.OrderNo
FROM Orders AS o
WHERE (SELECT COUNT(*)
FROM OrderLines AS ol
WHERE ol.OrderNo = o.OrderNo
AND o1.ProductName LIKE 'D%') = 1 ;
The alternative, that is especially useful if you have to display some
information from that single matching row (the productname in my
example) is to join the tables and add a NOT EXISTS subquery to search
for another matching row - the join eliminates orders without 'D'
products and the NOT EXISTS eliminates orders with more than one 'D'
product. The example below assumes that the combination of OrderNo and
OrderLineNo is the primary key for thhe OrderLines table.
SELECT o.OrderNo, ol.ProductName
FROM Orders AS o
INNER JOIN OrderLines AS ol
ON ol.OrderNo = o.OrderNo
AND ol.ProductName LIKE 'D%'
WHERE NOT EXISTS
(SELECT *
FROM OrderLines AS ol2
WHERE ol2.OrderNo = ol.OrderNo
AND ol2.ProductName LIKE 'D%'
AND ol2.OrderLineNo <> 0l.OrderLineNo) ;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.
- Prev by Date: Re: adding a query as parameter using 'contains' logic
- Previous by thread: Re: Cross tab based on DateDIFF
- Index(es):
Relevant Pages
|
|
