Re: [SQL] Using count on a join, group by required?

[Rob Sargent]

If you showed your work, you might get decent hints if not solutions.

On 12/14/2010 09:23 AM, Rob Sargent wrote:
> Shouldn't you be doing your own homework?
> 
> emaratiyya wrote:
>> Hi,Please help me solving this problem. I appreciate..Thankyou.
>>
>> Create the following table and insert few arbitrary records. Product
>> (product_id, product_name, supplier_name, quantity, price_per_unit)
>> You are required to create PL/SQL package that achieves the following
>> functionalities:
>>
>> •Obtaining the product supplier name by using the product_id
>> •Changing the price_per_unit by using the product_id
>> •When changing the price of the product, you have to have PL/SQL
>> code that
>> keeps the history of the prices. For this functionality you need to
>> create
>> audit table: product_audit(product_id, old_price, new_price,
>> date_of_change)
>> •Updating the quantity by using the product_id
>> •Get warning when the product level of stock (quantity) goes below
>> certain
>> threshold (e.g. 10)
>>
>>   

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Aggregating by unique values

[Lee Hachadoorian]

  
  
Perfect. Thanks, --Lee

On 12/14/2010 03:23 AM, Filip Rembiałkowski wrote:
try
  
  select zip, count(distinct id) from customer_service_date group by
  zip;
  
  2010/12/14 Lee Hachadoorian 
Hello,
  
  I'm trying to count customers who have received services by
  ZIP code,
  but I want to count each customer only once even though
  customers may
  have received services on multiple dates, and therefore appear
  in the
  table multiple times. There *is* a separate customers table,
  but because
  of dirty data, I cannot rely on it.
  
  The best I can come up with is:
  
  SELECT
     zip, count(*) AS count_serviced
  FROM
     (SELECT DISTINCT zip, id FROM customer_service_date) a
  GROUP BY
     zip
  ;
  
  The table (with some irrelevant fields dropped) is:
  
  CREATE TABLE customer_service_date
  (
   id integer,
   address character varying,
   city character varying,
   state character varying,
   zip character varying,
   service_date date
  )
  ;
  
  The table is missing a primary key field, but it would be (id,
  service_date) if it had one.
  
  Any suggestions to improve this?
  
  Thanks,
  --Lee
  
  --
  Lee Hachadoorian
  PhD Student, Geography
  Program in Earth & Environmental Sciences
  CUNY Graduate Center
  

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
  
  
  


-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

  

Re: [SQL] Using count on a join, group by required?

[Rob Sargent]

Shouldn't you be doing your own homework?

emaratiyya wrote:

Hi,Please help me solving this problem. I appreciate..Thankyou.

Create the following table and insert few arbitrary records. 
Product (product_id, product_name, supplier_name, quantity, price_per_unit)

You are required to create PL/SQL package that achieves the following
functionalities:

•   Obtaining the product supplier name by using the product_id
•   Changing the price_per_unit by using the product_id
•   When changing the price of the product, you have to have PL/SQL code 
that
keeps the history of the prices. For this functionality you need to create
audit table: product_audit(product_id, old_price, new_price, date_of_change)
•   Updating the quantity by using the product_id
•   Get warning when the product level of stock (quantity) goes below 
certain
threshold (e.g. 10)

  


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Using count on a join, group by required?

[Trinath Somanchi]

what are the inputs to the PL-SQL procedure.

On Tue, Dec 14, 2010 at 12:49 PM, emaratiyya  wrote:

>
> Hi,Please help me solving this problem. I appreciate..Thankyou.
>
> Create the following table and insert few arbitrary records.
> Product (product_id, product_name, supplier_name, quantity, price_per_unit)
> You are required to create PL/SQL package that achieves the following
> functionalities:
>
> •   Obtaining the product supplier name by using the product_id
> •   Changing the price_per_unit by using the product_id
> •   When changing the price of the product, you have to have PL/SQL
> code that
> keeps the history of the prices. For this functionality you need to create
> audit table: product_audit(product_id, old_price, new_price,
> date_of_change)
> •   Updating the quantity by using the product_id
> •   Get warning when the product level of stock (quantity) goes below
> certain
> threshold (e.g. 10)
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
Regards,
--
Trinath Somanchi,

Re: [SQL] Aggregating by unique values

[Filip Rembiałkowski]

try

select zip, count(distinct id) from customer_service_date group by zip;

2010/12/14 Lee Hachadoorian 

> Hello,
>
> I'm trying to count customers who have received services by ZIP code,
> but I want to count each customer only once even though customers may
> have received services on multiple dates, and therefore appear in the
> table multiple times. There *is* a separate customers table, but because
> of dirty data, I cannot rely on it.
>
> The best I can come up with is:
>
> SELECT
>zip, count(*) AS count_serviced
> FROM
>(SELECT DISTINCT zip, id FROM customer_service_date) a
> GROUP BY
>zip
> ;
>
> The table (with some irrelevant fields dropped) is:
>
> CREATE TABLE customer_service_date
> (
>  id integer,
>  address character varying,
>  city character varying,
>  state character varying,
>  zip character varying,
>  service_date date
> )
> ;
>
> The table is missing a primary key field, but it would be (id,
> service_date) if it had one.
>
> Any suggestions to improve this?
>
> Thanks,
> --Lee
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] Using count on a join, group by required?

[emaratiyya]

Hi,Please help me solving this problem. I appreciate..Thankyou.

Create the following table and insert few arbitrary records. 
Product (product_id, product_name, supplier_name, quantity, price_per_unit)
You are required to create PL/SQL package that achieves the following
functionalities:

•   Obtaining the product supplier name by using the product_id
•   Changing the price_per_unit by using the product_id
•   When changing the price of the product, you have to have PL/SQL code 
that
keeps the history of the prices. For this functionality you need to create
audit table: product_audit(product_id, old_price, new_price, date_of_change)
•   Updating the quantity by using the product_id
•   Get warning when the product level of stock (quantity) goes below 
certain
threshold (e.g. 10)

-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql