asked on

SQL Query with GROUP BY

Hello there,

I have this query which is working as I expect but only that some of the unit price is null, so the result is not combining them together. How can I combine them too. I tried ISNULL but still they are shown in different rows.below is my query and screenshot where the result rows are not grouped because of different unit price value.

cheers
Zolf

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Customer.organizationName,
    dbo.Receipt.transactionNumber,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Sale.actualPaymentDate,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity) as Quantity,
    ISNULL(dbo.Batch.unitPrice,0)
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.SaleDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-03-01'
GROUP BY
    dbo.Visitor.id,
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Customer.organizationName,
    dbo.Receipt.transactionNumber,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Sale.actualPaymentDate,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Batch.unitPrice

Open in new window

Mlanda T

In your GROUP BY, you are also grouping by dbo.Batch.unitPrice. That means each unit price will come out as a seperate set on its own as well. You need to rethink how you are handling the UnitPrice.

Zolf

ASKER

Thanks for your comment,but I also know that, I need help to find some work around this issue. If I try to remove the unitprice from the GROUP BY i get aggregation error which is obvious

Mlanda T

Before we can provide a work around, we need to understand your business logic around UnitPrice. There are many ways of handling UnitPrice. You can show the MAX, MIN, AVG, most recent UnitPrice and so on. We cannot jsut discard

Zolf

ASKER

i just need to show the unit price next to the product but the products which have discount in the db table i have not saved the unitprice as it is present with the actual qty bought.
how can i somehow ignore the unitprice in group by

Zolf

ASKER

how about using subqueries??

Mlanda T

How do you intend to handle the UnitPrice through time? For example, the UnitPrice might be $2.00 in January, then $3.00 in Feburary. Some transactions have a UnitPrice, others have a NULL. When someone runs your query in March... what should they see?

Mlanda T

The point I'm making here is really that it's a business rules issue first, before it is a SQL query issue. What is the business rule or expected behaviour? In this example, I have just used a MAX. Then it won't show the duplicate lines. But in the scenario I painted above, is this the correct thing to do according to your business requirements?

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Customer.organizationName,
    dbo.Receipt.transactionNumber,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Sale.actualPaymentDate,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity) as Quantity,
    MAX(SNULL(dbo.Batch.unitPrice,0)) UnitPrice
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.SaleDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-03-01'
GROUP BY
    dbo.Visitor.id,
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Customer.organizationName,
    dbo.Receipt.transactionNumber,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Sale.actualPaymentDate,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.Kol.code,
    dbo.Mohin.code
    --dbo.Batch.unitPrice --we no longer group by this, since we are going a MAX on it

Open in new window

awking00

Group by ...,
ISNULL(dbo.Batch.unitPrice,0)

Zolf

ASKER

awking00,

What are you trying to say,can you elaborate

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zolf

ASKER

cheers

awking00

What I was saying was that your original query was selecting isnull(dbo.Batch.unitPrice, 0) but was grouping by dbo.Batch.unitPrice and they needed to be the same.