Help with query

Hi,

I have a problem to write this query. I need to show a report which have employeeName, OrderTotal based on certain date.

However, eventhough that employee doesn't have an order yet, I will still show their name on the report with zero as orderTotal.

I have this query:

Select
EmployeeName,
Count(OrderID) as TotalOrder

from Employee WHERE InvoiceDT BETWEEN '1/1/2006' AND '1/31/2006'

But, this query would not return employeeName which are not between those invoiceDT.

So, how to get all employeeName, but filter the Order based on the invoiceDT.

Thanks in advance.

Anyone please...|||

Are you seeking for something like this?:

SELECT EmployeeName,Sum(TotalOrder) FROM

(SELECT EmployeeName,TotalOrder=CASE WHEN InvoiceDT BETWEEN '1996-08-06' AND '1998-01-05'
THEN Count(OrderID)
ELSE 0
END

FROM Employees
GROUP BY EmployeeName,InvoiceDT) as tmp
GROUP BY EmployeeName

|||Thanks lori, it works.|||Thanks lori, it works.

Help with query

Hello I need some help to make a query for these 2 tables:
TblEmployee
ID
EmployeeName
TblEmployeeChild
ID
SubjectID
Mark
I want to select Employeename, and count of tblEmployeeChild.ID where
subjectID > 5
But I also want to list all the employeeName and display count as 0.
Even though I use Left outer join, it only displays the EmployeeName for the
record for which the where condition is true.
How do I fix this ?
ThanksI'm just guessing because you've given us no information about primary
or foreign keys. Put the subjectid criteria after ON rather than WHERE:
SELECT E.id, E.employeename, COUNT(C.id)
FROM tblEmployee AS E
LEFT JOIN tblEmployeeChild AS C
ON E.id = C.id
AND C.subjectid > 5
GROUP BY E.id, E.employeename ;
David Portas
SQL Server MVP
--|||union your first query
to another query
which is not in your first query
then assigne the value to zero
--pseduocode--
first query
union
secondquery, where not in first query, count=0
--pseduocode--
"news.microsoft.com" wrote:

> Hello I need some help to make a query for these 2 tables:
> TblEmployee
> ID
> EmployeeName
>
> TblEmployeeChild
> ID
> SubjectID
> Mark
>
>
> I want to select Employeename, and count of tblEmployeeChild.ID where
> subjectID > 5
> But I also want to list all the employeeName and display count as 0.
> Even though I use Left outer join, it only displays the EmployeeName for t
he
> record for which the where condition is true.
> How do I fix this ?
> Thanks
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Plese stop using those silly :"tbl-" prefixes -- it violates ISO-11179
rules and there is only one data structure in SQL anyway. Do you really
have a single employee who is a piece of furniture? Why don't you know
that a row is not a record? Did you mean this:
CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
etc.);
CREATE TABLE Dependents
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
dependent_nbr INTEGER NOT NULL
CHECK (dependent_nbr > 0),
PRIMARY KEY (emp_id, dependent_nbr),
subject_id INTEGER NOT NULL
CHECK (subject_id > 0),
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
etc.);
subjectID > 5 <<
SELECT P.last_name, P.first_name,
COUNT(dependent_nbr) AS dependent_tally
FROM Personnel AS P
LEFT OUTER JOIN
Dependents AS D
ON D.subject_id > 5
AND P.emp_id = D.emp_id
GROUP BY P.last_name, P.first_name;|||Perfect!
Wroked like a charm, learnt we can put condition on the join itself, thanks
:)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124106846.435010.111810@.g47g2000cwa.googlegroups.com...
> I'm just guessing because you've given us no information about primary
> or foreign keys. Put the subjectid criteria after ON rather than WHERE:
> SELECT E.id, E.employeename, COUNT(C.id)
> FROM tblEmployee AS E
> LEFT JOIN tblEmployeeChild AS C
> ON E.id = C.id
> AND C.subjectid > 5
> GROUP BY E.id, E.employeename ;
> --
> David Portas
> SQL Server MVP
> --
>