Solved

Sql Criteria one to many

Posted on 2011-03-11
5
376 Views
Last Modified: 2012-06-22
Hello
I know this may be very simple but my brain does not seem to work today.
I have a sql query joining table A to Table B
it is a one to many where Table A as the unique records and table B has the multiple records that tie to each single record in A.
for example Table A  stores Tickets with a Ticket ID in it
Table B has all the detail items on that Ticket.
what i need to do is report on is in a time period
how many tickets there were issued and how many items per ticket.

So if there were 10 tickets last month with 40 items
i need to report the result as 10 tickets and 40 items.
what i have reporting know for a ticket count is 40  because it counts each detail record for each ticket.

How can i do this?
0
Comment
Question by:schuitkds
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35110510

select count(ticket), count(Items)
from Tablea
inner join tableb on Table1.ticket = tableb.ticket
group by tablea.ticket
0
 
LVL 18

Expert Comment

by:lludden
ID: 35110530

WITH cteTicketList (TicketID)
AS (SELECT TicketID FROM TicketList WHERE TicketDate BETWEEN @StartDate AND @EndDate),
cteTicketDetail (TicketID, Qty)
AS
(
SELECT TicketID, COUNT(*) AS Qty
FROM TicketDetail
WHERE TicketID IN (SELECT TIcketID FROM cteTicketList)
GROUP BY TicketID)
SELECT TicketList.*, cteTicketDetail.Qty
FROM TicketList
 INNER JOIN cteTicketList ON TicketList.TicketID = cteTicketList.TicketID
 LEFT OUTER JOIN cteTicketDetail ON TicketList.TicketID = cteTicketDetail.TicketID
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35110560

Or


select count(TableA.ID) as Tickets, (Select Count(ID) Items from tableB
                                                                   where TableB.ID = TableA.ID
                                                                   group by TableB.ID) Items
from TableA
group by ID
0
 
LVL 18

Expert Comment

by:lludden
ID: 35110732
My solution sales to any size of database.  If you have a million tickets in the table, you don't want to be looking at all of the detail on all of the orders if you are only interested in a small subset.
0
 
LVL 1

Expert Comment

by:rcharlton
ID: 35116149
Use an ordered analytical function, as follows:

SELECT      DISTINCT dbo.tbl_Ticket_Header.TicketID,
            COUNT(TicketID) OVER(PARTITION BY TicketID, NEWID()) AS TicketCount,
            COUNT(dbo.tbl_Ticket_Detail.TicketDetailID) OVER (PARTITION BY TicketID) AS DetailCount
FROM dbo.tbl_Ticket_Header WITH (NOLOCK)
      INNER JOIN dbo.tbl_Ticket_Detail WITH (NOLOCK)
      ON (dbo.tbl_Ticket_Header.TicketID = dbo.tbl_Ticket_Detail.TicketID)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Inserting oldest record into new table. 5 24
Return 0 on SQL count 24 30
Windows Services - Run a Program Grey Out 3 23
SQL Query Help Top 1 and Distinct? 6 26
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question