Re: SQL: Count() of DISTINCT codes???
Vince Teachout wrote: Ricardo Aráoz wrote: select code, count(code) ; from ( ; select order, code ; from codes ; group by order, code ; ) mycodes ; group by code Sweet. Damn, you're good. falls at Ricardo's feet I'm not worthy! I'm not worthy!/farf LOL ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
[NF] SQL: Count() of DISTINCT codes???
This is a little hard to explain, but I need to get a count of distinct codes within orders. For example: Order 1: code1 Code 1 code 1 code 2 code 3 Order 2: code 1 Code 3 I'd like to see: Code 1, 2 Code 2, 1 Code 3, 2 ie, distinct within orders, but then a count of codes for all orders. Anyway to do it in one shot? I'm pretty sure I can do it in 2 queries. Thanks. -- Vince Teachout Caracal Software www.caracal.net 518-733-9411 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: SQL: Count() of DISTINCT codes???
Vince Teachout writes: This is a little hard to explain, but I need to get a count of distinct codes within orders. For example: Order 1: code1 Code 1 code 1 code 2 code 3 Order 2: code 1 Code 3 I'd like to see: Code 1, 2 Code 2, 1 Code 3, 2 ie, distinct within orders, but then a count of codes for all orders. Anyway to do it in one shot? I'm pretty sure I can do it in 2 queries. Thanks. create cursor codes (order1 char(10), code char(10)) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 2) insert into codes values(Order 1, code 3) insert into codes values(Order 2, code 1) insert into codes values(Order 2, code 3) select code, count(code) from codes group by code HTH Jaime Vasquez Get your free 15 Mb POP3 email @alexandria.cc Click here - http://www.alexandria.cc/ ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] SQL: Count() of DISTINCT codes???
This is a little hard to explain, but I need to get a count of distinct codes within orders. For example: [snip] ie, distinct within orders, but then a count of codes for all orders. Anyway to do it in one shot? I'm pretty sure I can do it in 2 queries. Thanks. Something like SELECT DISTINCT codeno, COUNT(codeno) FROM (SELECT DISTINCT orderno+codeno, codeno FROM orders) This assumes that orderno and codeno are character fields; you can figure out how to concatenate them otherwise. -Jerry Wolper [EMAIL PROTECTED] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: SQL: Count() of DISTINCT codes???
Vince Teachout wrote: Jaime Vasquez wrote: Vince Teachout writes: This is a little hard to explain, but I need to get a count of distinct codes within orders. For example: Order 1: code1 Code 1 code 1 code 2 code 3 Order 2: code 1 Code 3 I'd like to see: Code 1, 2 Code 2, 1 Code 3, 2 ie, distinct within orders, but then a count of codes for all orders. Anyway to do it in one shot? I'm pretty sure I can do it in 2 queries. Thanks. create cursor codes (order1 char(10), code char(10)) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 1) insert into codes values(Order 1, code 2) insert into codes values(Order 1, code 3) insert into codes values(Order 2, code 1) insert into codes values(Order 2, code 3) select code, count(code) from codes group by code No, because I'm trying to get Count( distinct codes), so I'd want to see the results posted above. Your example just gives me a straight count: code 1, 4 code 2, 1 code 3, 2 I've already done it using 2 selects, was just curious about doing it in one. Thank you, though. select code, count(code) ; from ( ; select order, code ; from codes ; group by order, code ; ) mycodes ; group by code ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: SQL: Count() of DISTINCT codes???
Ricardo Aráoz wrote: select code, count(code) ; from ( ; select order, code ; from codes ; group by order, code ; ) mycodes ; group by code Sweet. Damn, you're good. falls at Ricardo's feet I'm not worthy! I'm not worthy!/farf -- Vince Teachout Caracal Software www.caracal.net 518-733-9411 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] SQL: Count() of DISTINCT codes???
Jerry Wolper wrote: SELECT DISTINCT codeno, COUNT(codeno) FROM (SELECT DISTINCT orderno+codeno, codeno FROM orders) This assumes that orderno and codeno are character fields; you can figure out how to concatenate them otherwise. Kudos to you for coming up with a correct answer first, but the judges must subtract 2 points because they (well, it's really just me) had to correct the code: SELECT code, COUNT(code) GROUP BY code FROM (SELECT DISTINCT order+code, code FROM codes) mycodes Thank you - TWO solutions. VFP rocks, SQL rocks! -- Vince Teachout Caracal Software www.caracal.net 518-733-9411 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
