Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Sql Criteria one to many

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
schuitkds
Asked:
schuitkds
  • 2
  • 2
1 Solution
 
Ephraim WangoyaCommented:

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

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
 
Ephraim WangoyaCommented:

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
 
lluddenCommented:
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
 
rcharltonCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now