Using SS2000, VS2003, RS2000.
I want to list all of the individual orders and how they were shipped. Then
I want to count the total number of orders and get a count for each method of
shipment. So, the data might look like this:
Order # Shipped
1 UPS
2 FedEx
3 USPS
4 FedEx
5 UPS
6 FedEx
Total Orders 6
FedEx 3
UPS 2
USPS 1
I tried adding a group and putting a filter in the group (FedEx) but it only
limited the detail part of the report to the rows that were shipped by FedEx.
Now I can't get rid of that filter. I even deleted all of the headers,
footers, groups and detail section and it still only gives me the rows with
FedEx.
Any ideas,
Thanks,
--
Dan D.I'm closer but not there yet. What I have now is this:
Order # Shipped
2 FedEx
4 FedEx
6 FedEx
Total Orders 3
FedEx 3
1 UPS
5 UPS
Total Orders 2
UPS 2
3 USPS
Total Orders 1
USPS 1
But what I need is what was in my original post
--
Dan D.
"Dan D." wrote:
> Using SS2000, VS2003, RS2000.
> I want to list all of the individual orders and how they were shipped. Then
> I want to count the total number of orders and get a count for each method of
> shipment. So, the data might look like this:
> Order # Shipped
> 1 UPS
> 2 FedEx
> 3 USPS
> 4 FedEx
> 5 UPS
> 6 FedEx
> Total Orders 6
> FedEx 3
> UPS 2
> USPS 1
> I tried adding a group and putting a filter in the group (FedEx) but it only
> limited the detail part of the report to the rows that were shipped by FedEx.
> Now I can't get rid of that filter. I even deleted all of the headers,
> footers, groups and detail section and it still only gives me the rows with
> FedEx.
> Any ideas,
> Thanks,
> --
> Dan D.|||Dan,
Can you do this with 2 datasets and 2 tables?
Dataset1:
SELECT orderID, carrier FROM orders ORDER BY orderID
Dataset2:
SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
carrier ORDER BY COUNT(orderID) DESC
You can use a header/footer row for the grand total.
-Josh
Dan D. wrote:
> I'm closer but not there yet. What I have now is this:
> Order # Shipped
> 2 FedEx
> 4 FedEx
> 6 FedEx
> Total Orders 3
> FedEx 3
> 1 UPS
> 5 UPS
> Total Orders 2
> UPS 2
> 3 USPS
> Total Orders 1
> USPS 1
> But what I need is what was in my original post
> --
> Dan D.
>
> "Dan D." wrote:
> > Using SS2000, VS2003, RS2000.
> > I want to list all of the individual orders and how they were shipped. Then
> > I want to count the total number of orders and get a count for each method of
> > shipment. So, the data might look like this:
> >
> > Order # Shipped
> > 1 UPS
> > 2 FedEx
> > 3 USPS
> > 4 FedEx
> > 5 UPS
> > 6 FedEx
> >
> > Total Orders 6
> > FedEx 3
> > UPS 2
> > USPS 1
> >
> > I tried adding a group and putting a filter in the group (FedEx) but it only
> > limited the detail part of the report to the rows that were shipped by FedEx.
> > Now I can't get rid of that filter. I even deleted all of the headers,
> > footers, groups and detail section and it still only gives me the rows with
> > FedEx.
> >
> > Any ideas,
> >
> > Thanks,
> > --
> > Dan D.|||It's worth a try. I'm wondering RS will keep the two datasets in sync. BTW, I
also posted a different example this morning under the subject "is this
possible in RS".
For the time being, I've created a another group on the carrier. Even though
it's not in the format the client wanted, it will give the counts they want.
I'll keep experimenting, though and try your idea.
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Can you do this with 2 datasets and 2 tables?
> Dataset1:
> SELECT orderID, carrier FROM orders ORDER BY orderID
> Dataset2:
> SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
> carrier ORDER BY COUNT(orderID) DESC
> You can use a header/footer row for the grand total.
> -Josh
>
> Dan D. wrote:
> > I'm closer but not there yet. What I have now is this:
> > Order # Shipped
> >
> > 2 FedEx
> > 4 FedEx
> > 6 FedEx
> > Total Orders 3
> > FedEx 3
> >
> > 1 UPS
> > 5 UPS
> > Total Orders 2
> > UPS 2
> >
> > 3 USPS
> > Total Orders 1
> > USPS 1
> >
> > But what I need is what was in my original post
> > --
> > Dan D.
> >
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, VS2003, RS2000.
> > > I want to list all of the individual orders and how they were shipped. Then
> > > I want to count the total number of orders and get a count for each method of
> > > shipment. So, the data might look like this:
> > >
> > > Order # Shipped
> > > 1 UPS
> > > 2 FedEx
> > > 3 USPS
> > > 4 FedEx
> > > 5 UPS
> > > 6 FedEx
> > >
> > > Total Orders 6
> > > FedEx 3
> > > UPS 2
> > > USPS 1
> > >
> > > I tried adding a group and putting a filter in the group (FedEx) but it only
> > > limited the detail part of the report to the rows that were shipped by FedEx.
> > > Now I can't get rid of that filter. I even deleted all of the headers,
> > > footers, groups and detail section and it still only gives me the rows with
> > > FedEx.
> > >
> > > Any ideas,
> > >
> > > Thanks,
> > > --
> > > Dan D.
>
Showing posts with label orders. Show all posts
Showing posts with label orders. Show all posts
Sunday, February 19, 2012
Friday, February 17, 2012
Counting query
Hi,
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>
Counting Orders in Sub-Select Query?
Hi,
I am summarizing data like this
SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') AS
[Year-Month], dbo_Order_Line_Invoice.VendId,
Sum(dbo_Order_Line_Invoice.Sales) AS Sales_Total, Sum([Sales]-[Cost]) AS
GM_Total, Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count
FROM dbo_Order_Line_Invoice
WHERE (((dbo_Order_Line_Invoice.InvoiceDate)>=#8/1/2004# And
(dbo_Order_Line_Invoice.InvoiceDate)<=#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00'),
dbo_Order_Line_Invoice.VendId;
But I also want to count the number of orders. I am thinking I could
use a sub query to select just the orders for the above realtions, group
on ordernumber then Count(*) but I don't know how to actually do this.
Can someone help me?
TIA,
DanThat isn't a SQL Server query. If you are using SQL Server then take a
look at the CUBE / ROLLUP operator.
David Portas
SQL Server MVP
--
I am summarizing data like this
SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') AS
[Year-Month], dbo_Order_Line_Invoice.VendId,
Sum(dbo_Order_Line_Invoice.Sales) AS Sales_Total, Sum([Sales]-[Cost]) AS
GM_Total, Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count
FROM dbo_Order_Line_Invoice
WHERE (((dbo_Order_Line_Invoice.InvoiceDate)>=#8/1/2004# And
(dbo_Order_Line_Invoice.InvoiceDate)<=#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00'),
dbo_Order_Line_Invoice.VendId;
But I also want to count the number of orders. I am thinking I could
use a sub query to select just the orders for the above realtions, group
on ordernumber then Count(*) but I don't know how to actually do this.
Can someone help me?
TIA,
DanThat isn't a SQL Server query. If you are using SQL Server then take a
look at the CUBE / ROLLUP operator.
David Portas
SQL Server MVP
--
Counting items and returning values
I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB
need to return how many times each platform is listed in the DB
Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION
I'd like the data to be returned as
XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1
How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name
HTH,
-Cliff
"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?
Tuesday, February 14, 2012
Counting dups
When I run this query:
SELECT orderid, count(*) AS 'DupCount'
FROM orders
GROUP BY orderid
HAVING count(*) > 1
I get this:
Orderid DupCount
-- --
1 2
2 5
3 2
How can I return the count of dup records ( in this case 3) and the total
number of dups (in this case 9)?
Can I use ROLLUP with HAVING?SELECT COUNT(*), SUM(dupcount)
FROM
(SELECT orderid, COUNT(*) AS dupcount
FROM orders
GROUP BY orderid
HAVING COUNT(*) > 1) AS T
--
David Portas
SQL Server MVP
--|||Thanks David
Do you know if it is possible to use WITH ROLLUP with a HAVING clause. To
me it appears that the ROLLUP will return results independant of the HAVING.
"Dave" <david.frickNOtoSPAM@.homestore.com> wrote in message
news:unkRiNPoEHA.3876@.TK2MSFTNGP15.phx.gbl...
> When I run this query:
> SELECT orderid, count(*) AS 'DupCount'
> FROM orders
> GROUP BY orderid
> HAVING count(*) > 1
> I get this:
> Orderid DupCount
> -- --
> 1 2
> 2 5
> 3 2
> How can I return the count of dup records ( in this case 3) and the total
> number of dups (in this case 9)?
> Can I use ROLLUP with HAVING?
>
>|||Yes you can use CUBE/ROLLUP and HAVING together. The GROUPING() function is
useful in conjuction with ROLLUP and HAVING to filter the required summary
rows from the query.
I don't think ROLLUP will help much with your query because I believe you'll
still need a subquery to do a "double aggregation".
--
David Portas
SQL Server MVP
--
SELECT orderid, count(*) AS 'DupCount'
FROM orders
GROUP BY orderid
HAVING count(*) > 1
I get this:
Orderid DupCount
-- --
1 2
2 5
3 2
How can I return the count of dup records ( in this case 3) and the total
number of dups (in this case 9)?
Can I use ROLLUP with HAVING?SELECT COUNT(*), SUM(dupcount)
FROM
(SELECT orderid, COUNT(*) AS dupcount
FROM orders
GROUP BY orderid
HAVING COUNT(*) > 1) AS T
--
David Portas
SQL Server MVP
--|||Thanks David
Do you know if it is possible to use WITH ROLLUP with a HAVING clause. To
me it appears that the ROLLUP will return results independant of the HAVING.
"Dave" <david.frickNOtoSPAM@.homestore.com> wrote in message
news:unkRiNPoEHA.3876@.TK2MSFTNGP15.phx.gbl...
> When I run this query:
> SELECT orderid, count(*) AS 'DupCount'
> FROM orders
> GROUP BY orderid
> HAVING count(*) > 1
> I get this:
> Orderid DupCount
> -- --
> 1 2
> 2 5
> 3 2
> How can I return the count of dup records ( in this case 3) and the total
> number of dups (in this case 9)?
> Can I use ROLLUP with HAVING?
>
>|||Yes you can use CUBE/ROLLUP and HAVING together. The GROUPING() function is
useful in conjuction with ROLLUP and HAVING to filter the required summary
rows from the query.
I don't think ROLLUP will help much with your query because I believe you'll
still need a subquery to do a "double aggregation".
--
David Portas
SQL Server MVP
--
Counting a group of data as one
How do I go about counting a group of data as 1 item? For example, I have a user who worked on 11 work orders, but only 10 were completed and I am trying to get the # of work orders completed. The problem I am having is it is counting each work day for each completed work order, so for example, if one work order was worked on for 5 days, it counts that work order as five and not one. I need it to ignore the number of work days and just give me the one work order in the count.use distinct count instead.|||I tried that, but it does not work in this case. I am getting closer with it, but now I have a different problem. It seems that the total I need is always off by 1. For example, if a person worked on 7 work orders and 5 of them are closed or completed, it is giving me a count of six. I need to figure out how if a work order has a certain status, to take that total and subtract 1 from it, otherwise leave it alone. Any ideas?|||It would be good if you would post an example of your current report and your desired result.|||You might be able to use a running total with a formula to determine which records to count; reset at the appropriate level.
Subscribe to:
Posts (Atom)