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?
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.
0
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
0
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
awking00Commented:
Group by ...,
ISNULL(dbo.Batch.unitPrice,0)
0
zolfAuthor Commented:
awking00,

What are you trying to say,can you elaborate
0
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.
0

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
0
awking00Commented:
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.