Help with Count

Hi all!

I have a table that has Order Number and an Account Number, I want to count all the account numbers. However there might be more then one order number with the same account number...

See example:

Table:

[Order Number] [Account Number]
12312 1234
13231 2342
14352 1311
23423 1313
11422 1234

Output should be: 4

I want to count([account number]) and get 4, notice there are 5 rows, the 1st and last have the same account number.

How do I write this query?

Thanks,

KenFound the anwser I was looking for!

select count(distinct [account number]) from table|||select count(distinct [Account Number])
from tbl|||Good timing! I found the answer just as you posted!

Thanks for the reply!

Ken

Help with a stored procedure or DTS package

Can anyone offer me a solution to this , i have a table that hold
QI AN Quantity Price Order Refer
Area
28 96229392 15 83.98 1 A1
Level 1
28 960004877 55 192.68 2 B
Level 1
28 96011194 56 102.66 3 B1
Level 1
28 96011194 112 10.66 3 C
Level 2
and i want to transform it to
QI AN Quantity Price Refer
Area
28 grupanfa 0
Level 1
28 96229392 15 83.98 A1
28 960004877 55 192.68 B
28 96011194 56 102.66 B1
28 grupenda 0
28 grupanfa 0
Level 2
28 96011194 112 10.66 C
28 grupenda 0
can anyone please advise
Regards
JohnAre both these tables in sql server? what are the data types?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>|||The first talbe is in SQL, the second need creating as a temporary table,
can assume all columns are varchar(50)
Regards
john
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:8tOdnTrKJqZY1fbZRVnyuw@.bt.com...
> Are both these tables in sql server? what are the data types?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "John" <topguy75@.hotmail.com> wrote in message
> news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
>|||Where are groupenda and groupanfa coming from? Are they indicating the
start and end of groups, based on level? Why do these have a value of 0,
and is the 0 supposed to represent anything?
Are you trying to create a flat file for an export to another system?
This looks like something that you should be doing in an application or
report, rather than in SQL.
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>sql

HELP with a Select/Union statement

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);

This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;

I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE ZIPCODE COUNTRY SHIP_VIA ID
DIEBOLD INC (4076A) ATTN: RANCE AARON 343 MANOR DR PACIFICA CA 94044 18932
DIEBOLD, INC OH UPS #88X08X 18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!...I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.Define what you mean by "first line". The order of data in a database has no meaning. Do you have a date field or some other value you can sort on to determine which shipping information is "first"?|||Sorry
In the Union query the first select statement will Always be the correct shipping information when the ORDER form has one(Shipping info) if it does not have a Shipping address printed on the form then the second select statement will pull from the bill table which will only have one record per the order number.

Example Order form:
Bill-to Ship to information
Bill to Compnay Ship to company
Bill to address Ship to address
Bill to city Ship to City
Bill to State Ship to state
Etc Etc

When this order occurs then there is one record in the bill to table with that info and 1 record in the Ship to table with the shipping info This is where the problme occurs when I do the UNION it looks in the Shipping table first and pulls the shipping info and then the other statement in the UNION pulls the BILL to info which give me 2 records in the Query so what happends in this case I pull the info using ODBC to my shipping system and it says it found 2 records but I only what the First select statement to show. Another problem is when the ship to is not fill out I need to use the second part of the UNION select statement in order ot pull the bill to info for the shipping info but this works correctly due to only 1 order number exists in the bill to tableEX:

Example Order form:
Bill-to Ship to information
Bill to Compnay SAME
Bill to address
Bill to city
Bill to State
Etc
When this occurs then I need to use the Bill to table for the shipping info.

Hopfully this clears things up.|||So this query:SELECT Cust_address.NAME,
Cust_address.ADDR_1,
Cust_address.ADDR_2,
Cust_address.ADDR_3,
Cust_address.CITY,
Cust_address.STATE,
Cust_address.ZIPCODE,
Cust_address.COUNTRY,
Cust_address.SHIP_VIA,
customer_order.ID
FROM Cust_address
INNER JOIN customer_order
ON Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID
AND Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO...contains all the orders that you need, but in some cases the address information is missing and you want to pull address information from the CUSTOMER table to fill it in?|||Yes Sir that is correct!

help with a SELECT

Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JCTry:
select
o.*
from
Orders o
join
(
select
idCLient
, max (orderDate) orderDate
from
Orders
group by
idCLient
) x on x.idClient = o.idClient
and x.orderDate = o.orderDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.
for example:
Table:
idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004
and the SELECT should return the next records:
idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004
Hope someone can help me with this :)
Thanks in advance
JC|||Thanks Tom, worked fine... sorry for multi posting
JC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uweVaEsHFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Try:
> select
> o.*
> from
> Orders o
> join
> (
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
> news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a table that contains orders from cliente. Suppose that each
> client has more than one order, I would like to write a SELECT statement
> that returns the order with the oldest date from each client.
> for example:
> Table:
> idClient idOrder orderDate
> 35 100 03/10/2004
> 35 120 03/22/2004
> 35 150 04/10/2004
> 72 45 01/10/2004
> 72 56 01/29/2004
> and the SELECT should return the next records:
> idClient idOrder orderDate
> 35 150 04/10/2004
> 72 56 01/29/2004
> Hope someone can help me with this :)
> Thanks in advance
> JC
>
>|||Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Good catch!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Thanks Steve, you're right...MIN is the choice for the oldest date. It
worked fine too.
JC
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Tiny detail - the specs were to retrieve the oldest order
> for each client, not the newest, so you'll want MIN, not
> MAX.
> Another solution is
> select o.*
> from Orders as O1
> where orderDate = (
> select min(orderDate)
> from Orders as O2
> where O2.idClient = O1.idClient
> )
> One or the other formulation may be faster, but what
> I like about this one is that it follows the English description
> closely: select all orders where the order date is
> the earliest for that client.
> Note that there may not be just one earliest order
> for a given client, and each of these queries will
> return all orders with minimum orderDate for the client.
> Steve Kass
> Drew University
> Tom Moreau wrote:
>

Help suppress

I am trying to suppress some details, what I have is 3 columns 1st is order number, 2nd is product, 3rd stock status ie allocated/back ordered. It prints a line per product and the status. What I am trying to do is if the order has one line on back order then I want to suppress the whole order.

Thanks

RichardHi,
Can you post the expected result with some sample data?

Madhivanan|||What version on Crystal are you using? I use 8.5, so you may need to modify the following to work with your version.

You can use a conditional suppress. I will assume that you have each order appearing in the Details section. Go into the Format of the details section. Find where you can check to Suppress that section. You should see a button that looks like 'x-2'. Click the button and enter a formula. The Detail section will be supressed each time the formula evaluates to True.

Example, put this in the Formula box when you click 'x-2':
{StockStatus} = "BackOrdered"