CODEWITHSUNDEEP
sqlsql-serversql-server-2008window-functions
user1314348
user1314348

Reputation: 1

How to count orders placed by a Indivisual customers?

I have table 

Order(orderid, customerid, billingcompanyname, billingfirstname,
 billinglastname, billingcountry, shipcountry, paymentamount, 
 creditcardtransactionid, orderdate, creditcardauthorizationdate, orderstatus, 
 total_payment_received, tax1_title salestax1)

customerid is a foreign key.

I need count of orders placed by an individual customer with the Company name and above all fields in the output.

Upvotes: 0

Views: 7245

Answers (5)

S Nagendra
S Nagendra

Reputation: 49

Using SQL

To count orders placed by individual customers, we can apply count() operation with windows function and which will give us desired output: lets try this: as in the input we have customer_id is FK.

To count orders placed by indivudual customer:

select cust_id,cnt as orders_cnt,
dense_rank() over(oreder by cnt as desc) as drnk 
from (select order_id,cust_id,count(*) over(partition by 
cust_id) as cnt from orders_table) as x;

To list customers details who had ordered orders in count wise/maximum orders:

select * from 
(select cust_id,cnt,dense_rank() over(order by cnt desc) as drnk from
(select order_id,cust_id,count(*) over(partition by cust_id) as cnt from
orders_table) as x) as xx
join customer_table c
on c.cust_id = xx.cust_id where drnk = 1

Upvotes: 0

MontyPython
MontyPython

Reputation: 2994

Use this.

with customers as (select '1424' id_customer, '13-Feb-15' date_purchase, 'Petr' name_first, 'Kellner' name_last, 'Chicago' name_city from dual
union all
select '1425' id_customer, '13-Feb-15' date_purchase, 'Shelley' name_first, 'Birdwick' name_last, 'San Jose' name_city from dual
union all
select '1426' id_customer, '13-Feb-15' date_purchase, 'Morris' name_first, 'Moore' name_last, 'San Fransisco' name_city from dual
union all
select '1427' id_customer, '13-Feb-15' date_purchase, 'Shyam' name_first, 'Bajaj' name_last, 'Detroit' name_city from dual
union all
select '1428' id_customer, '13-Feb-15' date_purchase, 'Xu' name_first, 'Wang' name_last, 'New York' name_city from dual),

orders as (select '1224215' id_order, '1425' id_customer, '13-Feb-15' date_purchase, '235' amount_product, 'Name of Book' name_product from dual
union all
select '1224216' id_order, '1424' id_customer, '13-Feb-15' date_purchase, '356' amount_product, 'Name of Book' name_product from dual
union all
select '1224217' id_order, '1426' id_customer, '13-Feb-15' date_purchase, '263' amount_product, 'Name of Book' name_product from dual
union all
select '1224218' id_order, '1426' id_customer, '13-Feb-15' date_purchase, '326' amount_product, 'Name of Book' name_product from dual
union all
select '1224219' id_order, '1427' id_customer, '13-Feb-15' date_purchase, '236' amount_product, 'Name of Book' name_product from dual
union all
select '1224220' id_order, '1428' id_customer, '13-Feb-15' date_purchase, '233' amount_product, 'Name of Book' name_product from dual
union all
select '1224221' id_order, '1426' id_customer, '13-Feb-15' date_purchase, '633' amount_product, 'Name of Book' name_product from dual
union all
select '1224222' id_order, '1424' id_customer, '13-Feb-15' date_purchase, '235' amount_product, 'Name of Book' name_product from dual
union all
select '1224215' id_order, '1426' id_customer, '13-Feb-15' date_purchase, '632' amount_product, 'Name of Book' name_product from dual
union all
select '1224215' id_order, '1425' id_customer, '13-Feb-15' date_purchase, '236' amount_product, 'Name of Book' name_product from dual)

select customers.name_first, customers.name_last, count(distinct id_order) orders, sum(amount_product) total_amount
from customers left join orders on customers.id_customer = orders.id_customer group by customers.name_first, customers.name_last;

Upvotes: 0

Diego
Diego

Reputation: 36166

do you have the companyName field on the orders table? You should have a company table and a companyId on the orders table.

Anyway, in this scenario (and if a customers ALWAYS belong to the same company), you can simple do this:

select customerid, billingcompanyname, count(*)
from orders
group by customerid, billingcompanyname

Upvotes: 1

Joseph Victor Zammit
Joseph Victor Zammit

Reputation: 15320

To achieve such result you have to GROUP BY the customerid but you cannot have the orderid within the results set as the COUNT has to run on the distinct values of orderid.

Upvotes: 1

McGarnagle
McGarnagle

Reputation: 102793

Try this:

SELECT o1.cnt, o2.*
FROM (
    SELECT COUNT(*) cnt, customerid FROM order GROUP BY customerid
) o1
INNER JOIN order o2 on o1.customerid = o2.customerid

Or even better:

SELECT order.*, COUNT(*) OVER (PARTITION BY customerid) AS cnt
FROM order

Upvotes: 2

Related Questions