Can anyone tell me how I go about building a query which shows Quantity
Ordered for two different Years?

We have an Orders Table. From this we want to show the Quantity of items
ordered for 2002 in one column, and the Quantity of items ordered for 2003
in another. We have a field called OrderDate which I'm using the Year(date)
command to select just the year.

This is easy for just the one year - do I need to create a sub-query for one
year, and then build this into my main query to show the other year? I was
trying to complete this with just one query.

Any advice would be great.


Cheers
Craig.


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

SELECT [year], SUM([qty]) AS [total_qty]
  FROM (SELECT DATEPART(yyyy, [date]) AS [year], [qty]
    FROM [orders]) x
  WHERE [year] IN (2002, 2003)
  GROUP BY [year]

That oughta do it.

Thanks Skip - will give that a try...


-----Original Message-----
From: skip@f... [mailto:skip@f...]
Sent: 22 March 2003 03:45
To: ASP Databases
Subject: [asp_databases] Re: Query problem


SELECT [year], SUM([qty]) AS [total_qty]
  FROM (SELECT DATEPART(yyyy, [date]) AS [year], [qty]
    FROM [orders]) x
  WHERE [year] IN (2002, 2003)
  GROUP BY [year]

That oughta do it.

_____________________________________________________________________
Please contact I.T. Support if you have received this email in error.
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________

Thanks Skip - that worked.

How would I alter this SQL to move the Year result into it's own column, for
example


Product		Qty Year 02	Qty Year 03
----------------------------------------------------
xyz123		8940		890
abc987		9038		304


Where as at the moment, it's formatting it as:

Year	Product		Total Qty
----------------------------------------
2002	xyz123		8940
2003	xyz123		890
2002	abc987		9038
2003	abc987		304


Hope this is clear. I'd be greatful if you could point me in the right
direction.

Cheers
Craig.




-----Original Message-----
From: skip@f... [mailto:skip@f...]
Sent: 22 March 2003 03:45
To: ASP Databases
Subject: [asp_databases] Re: Query problem


SELECT [year], SUM([qty]) AS [total_qty]
  FROM (SELECT DATEPART(yyyy, [date]) AS [year], [qty]
    FROM [orders]) x
  WHERE [year] IN (2002, 2003)
  GROUP BY [year]

That oughta do it.

_____________________________________________________________________
Please contact I.T. Support if you have received this email in error.
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________


_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer

Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.

Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.

Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________