CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

Treat multiple lines as one item in SQL Server

I have an invoice_detail table that stores all invoice information. Obviously the detail table stores each line item, to break out the invoice like this:

Ticket_Detail_ID   Ticket_Number   Customer_ID   Service_Code  Total
     1                1                 15           Book1       4.00
     2                1                 15           Book2       5.00
     3                1                 15           Book3       6.00
     4                2                 16           Book1       4.00
     5                2                 16           Book2       5.00
     6                3                 17           Book1       4.00
     7                3                 17           Book2       5.00
     8                3                 17           Book3       6.00

I want to Select a count of distinct tickets based on Ticket_number That does not have a "Book3" service code. So in this example I would count:

Ticket 16, since it did not have a "Book3"

It would return:

1

My query right now is:

Select Count (Distinct Ticket_Number) as Total
From Invoice_Details
Where Service_Code <> 'Book3'

This returns:

6

Upvotes: 2

Views: 133

Answers (1)

Lamak
Lamak

Reputation: 70648

Use NOT EXISTS:

SELECT COUNT(DISTINCT Ticket_Number)
FROM dbo.YourTable T
WHERE NOT EXISTS(SELECT 1 FROM dbo.YourTable
                 WHERE Service_Code = 'Book3'
                 AND Ticket_Number = T.Ticket_Number)

Here is an sqlfiddle with a demo of this.

Upvotes: 2

Related Questions