CODEWITHSUNDEEP
sql
Rash
Rash

Reputation: 896

Sql query to select records for a single distinct customer id

I have written a query for sales by customers in groups it is as follows:

SELECT customerid,
SUM (salestax1)As total_salestax1,
SUM(total_payment_received) As total_payment_recieved,
COUNT (orderid)as order_qty,
SUM(paymentamount)As paymentamount
 FROM Orders_74V94W6D22$
WHERE orderdate between '7/6/2011 16:35' and '2/3/2012 11:53'    
GROUP BY customerid

but this query shows only 5 fields but I need to show following fields: 

orderid    billingcompanyname    billingfirstname    billinglastname    
billingcountry    shipcountry    paymentamount creditcardtransactionid    
orderdate    creditcardauthorizationdate    orderstatus    
total_payment_received    tax1_title salestax1

then how to deal with it?

Upvotes: 0

Views: 2914

Answers (4)

Judith Mendes
Judith Mendes

Reputation: 1

SELECT
      vehicle_maker,
      COUNT(DISTINCT cust.customer_id) AS number_of_customers
FROM product_t pro
JOIN order_t ord     ON pro.product_id  = ord.product_id
JOIN customer_t cust ON ord.customer_id = cust.customer_id
GROUP BY vehicle_maker
ORDER BY number_of_customers DESC
LIMIT 5;

Upvotes: 0

Laurie Li
Laurie Li

Reputation: 1

You can add the fields you need to SELECT and GROUP BY like this:

SELECT
    customerid,
    orderid,
    billingcompanyname,
    billingfirstname,
    billinglastname,
    billingcountry,
    shipcountry,
    paymentamount,
    creditcardtransactionid,
    orderdate,
    creditcardauthorizationdate,
    orderstatus,
    total_payment_received,
    tax1_title,
    salestax1,
    SUM (salestax1) As total_salestax1,
    SUM (total_payment_received) As total_payment_recieved,
    COUNT (orderid) AS order_qty,
    SUM (paymentamount) As paymentamount
FROM Orders_74V94W6D22$
WHERE orderdate between '7/6/2011 16:35' and '2/3/2012 11:53'
GROUP BY
    customerid,
    orderid,
    billingcompanyname,
    billingfirstname,
    billinglastname,
    billingcountry,
    shipcountry,
    paymentamount,
    creditcardtransactionid,
    orderdate,
    creditcardauthorizationdate,
    orderstatus,
    total_payment_received,
    tax1_title,
    salestax1

But I hope you truly understand what will happen.

Your original SQL means you want to calculate the tax, received payment, order amount, and payment amount for each customer identified by customerid. Everything looks fine there. But after you add fields to SELECT and GROUP BY, you calculate SUM and COUNT for each customer, order, company name, ... And it even makes COUNT(orderid) AS order_qty redundant, because the result will always be 1 and not a meaningful 1.

That's why I guess that you might misunderstand your original SQL, or you need a double check about the final result you want.

Upvotes: 0

Diego
Diego

Reputation: 36166

you need to understand what GROUP BY means. If you are grouping by customerId, you will have only one customer because all data is grouped into it.

How do you want to group by orderid and display the orderid on your result set? If you have 10 order ids, do you expect 10 rows on the result? If yes, fine, group by it but I don't think that's what you want

EDIT:

Well, this is NOT a good idea, your table structure is WRONG and I dont think you fully understand that a group by means, BUT I think this query will get your result:

 SELECT customerid, 
 (select top 1  [column1] from Orders_74V94W6D22$ where customerid = ORD.customerid),
 (select top 1  [column2] from Orders_74V94W6D22$ where customerid = ORD.customerid),
 (select top 1  [column3] from Orders_74V94W6D22$ where customerid = ORD.customerid),
 SUM (salestax1)As total_salestax1, 
 SUM(total_payment_received) As total_payment_recieved, 
 COUNT (orderid)as order_qty, 
 SUM(paymentamount)As paymentamount
 FROM Orders_74V94W6D22$  ORD
 WHERE orderdate between '7/6/2011 16:35' and '2/3/2012 11:53' 
 GROUP BY customerid

Upvotes: 1

jenson-button-event
jenson-button-event

Reputation: 18961

To select more about the customer, you need to use your query as a sub query, something like:

Select distinct c.[column1], c.[column2], c.[column3], tbl.*
From Orders_74V94W6D22$ c inner join (
 SELECT customerid,
SUM (salestax1)As total_salestax1,
SUM(total_payment_received) As total_payment_recieved,
COUNT (orderid)as order_qty,
SUM(paymentamount)As paymentamount
 FROM Orders_74V94W6D22$
WHERE orderdate between '7/6/2011 16:35' and '2/3/2012 11:53'    
GROUP BY customerid 
) as tbl on tbl.customerid = c.customerid

but you cant logically select something about 1 order as youve grouped multiple orders

Upvotes: 1

Related Questions