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


1
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
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.
zolfAuthor Commented:
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
MlandaTCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

zolfAuthor Commented:
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
zolfAuthor Commented:
how about using subqueries??
MlandaTCommented:
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?
MlandaTCommented:
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

awking00Information Technology SpecialistCommented:
Group by ...,
ISNULL(dbo.Batch.unitPrice,0)
zolfAuthor Commented:
awking00,

What are you trying to say,can you elaborate
MlandaTCommented:
I think that for as long as you are grouping by UnitPrice, in any variation, you are going to have multiple lines for each possible value of it. There has to be a calculation on it in order to achieve what you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
cheers
awking00Information Technology SpecialistCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.