SQL reporting query

Question

I have a database with following structure.

CREATE TABLE Party
    (
      PartyID INT IDENTITY
                  PRIMARY KEY ,
      StatusID INT ,
      Weigth INT ,
      OldWeigth INT
    );
GO
CREATE TABLE PartyLocation
    (
      PartyLocationID INT IDENTITY
                          PRIMARY KEY ,
      PartyID INT FOREIGN KEY REFERENCES dbo.Party ( PartyID ) ,
      LocationID INT ,
      Distance INT
    );
GO
CREATE TABLE PartyRole
    (
      PartyRoleID INT IDENTITY
                      PRIMARY KEY ,
      PartyID INT FOREIGN KEY REFERENCES dbo.Party ( PartyID ) ,
      RoleID INT
    );

with some simple data.

INSERT  INTO dbo.Party
        ( StatusID, Weigth, OldWeigth )
VALUES  ( 1, -- StatusID - int
          10, -- Age - int
          20 -- OldAge - int
          ),
        ( 1, 15, 25 ),
        ( 2, 20, 30 );

INSERT  INTO dbo.PartyLocation
        ( PartyID, LocationID, Distance )
VALUES  ( 1, -- PartyID - int
          1, -- LocationID - int
          100  -- Distance - int
          ),
        ( 1, 2, 200 ),
        ( 1, 3, 300 ),
        ( 2, 1, 1000 ),
        ( 2, 2, 2000 ),
        ( 3, 1, 10000 );

INSERT  INTO dbo.PartyRole
        ( PartyID, RoleID )
VALUES  ( 1, -- PartyID - int
          1  -- RoleID - int
          ),
        ( 1, 2 ),
        ( 1, 3 ),
        ( 2, 1 ),
        ( 2, 2 ),
        ( 3, 1 );

I want to query the following information

Return sum of Weigth of all parties that has roleID = 1 in PartyRole table
Return sum of OldWeigth of all parties that has statusID = 2
Return sum of distances of all parties that has locationID = 3
Return sum of distances of all parties that has roleID = 2

So the expected results are

FilteredWeigth FilteredOldWeigth FilteredDistance AnotherFilteredDistance
-------------- ----------------- ---------------- -----------------------
45             30                600              3600

Can we write a query that will query each table just once? If no what will be the most optimal way to query the data?

In `Party` table, you are not inserting anything into `PartyID` but only to `StatusID`, nonetheless a nicely explained question — vishwarajanand, Nov 09 '17 at 11:19

Serkan Arslan · Accepted Answer · 2017-11-09T12:47:10.587

2

You can try this.

SELECT 
    FilteredWeigth = SUM(CASE WHEN  RoleID = 1 AND RN_P = 1 THEN Weigth END) ,
    FilteredOldWeigth = SUM(CASE WHEN StatusID = 2 AND RN_P = 1 THEN OldWeigth END), 
    FilteredDistance = SUM(CASE WHEN  LocationID = 3 AND RN_L = 1 THEN Distance END), 
    AnotherFilteredDistance = SUM(CASE WHEN RoleID = 2 THEN Distance END) 
 FROM (
        SELECT  P.Weigth, P.StatusID, P.OldWeigth, PL.LocationID, PL.Distance, PR.RoleID, 
                RN_P = ROW_NUMBER() OVER (PARTITION BY P.PartyID ORDER BY PL.PartyLocationID),
                RN_L = ROW_NUMBER() OVER (PARTITION BY PL.LocationID ORDER BY PR.PartyRoleID)
        FROM Party P
        INNER JOIN PartyLocation PL ON P.PartyID = PL.PartyID
        INNER JOIN PartyRole PR ON P.PartyID = PR.PartyID
   ) AS T

edited Nov 09 '17 at 12:47

answered Nov 09 '17 at 11:20

Serkan Arslan

13,158
4
29
44

why you put CASE WHEN LocationID = 1 AND RoleID = 1 in the FilteredWeigth, I only want to filter based on RoleID. – Artashes Khachatryan Nov 09 '17 at 11:56
Because of PartyLocation join, every role row multiply with PartyLocation row. For getting single Party row i added LocationID = 1 filter. – Serkan Arslan Nov 09 '17 at 12:16
Yes, I understand that, but what if there are no corresponding row in partylocation with LocationID = 1? I mean I need to generate this query from the application side, how can I get that '1' value ? – Artashes Khachatryan Nov 09 '17 at 12:23
You have not updated FilteredOldWeigth = SUM(CASE WHEN LocationID = 1 AND StatusID = 2 THEN OldWeigth END) this part – Artashes Khachatryan Nov 09 '17 at 12:41
I added row_number to this fields too. – Serkan Arslan Nov 09 '17 at 12:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/158603/discussion-between-artashes-khachatryan-and-sarslan). – Artashes Khachatryan Nov 09 '17 at 12:50

Ab Bennett · Answer 2 · 2017-11-09T11:21:38.853

0

the below gives

45 20 300 3600

the third column gives 300 which does not correspond to your expected result.

with q1
as
(
   select sum(weigth) FilteredWeigth
   from party join partyrole on party.partyid = partyrole.partyid
   where partyrole.RoleID = '1'
),
q2 as
(
   select sum(weigth) OldWeigth from party where StatusID = '2'
),
q3 as (
   select sum(Distance) FilteredDistance
   from party join PartyLocation on party.partyid = PartyLocation.partyid
   where PartyLocation.locationID = '3'
),
q4 as 
(
    select sum(Distance) AnotherFilteredDistance
    from party join partyrole on party.partyid = partyrole.partyid
               join PartyLocation on party.partyid = PartyLocation.partyid
    where partyrole.RoleID = '2'
)
select FilteredWeigth,OldWeigth,FilteredDistance,AnotherFilteredDistance 
from q1,q2,q3,q4

edited Nov 09 '17 at 11:21

answered Nov 09 '17 at 11:16

Ab Bennett

1,391
17
24

You have accessed the party table 4 times.... I think there must be more optimal way to get the results. – Artashes Khachatryan Nov 09 '17 at 11:42
very true, but if the data is not alot as above. if this was 1 million rows this is not optimal. I was assuming his data size was bugger all @sarslan answer is alot better, but harder to read..its a trade off – Ab Bennett Nov 09 '17 at 11:44

Jayasurya Satheesh · Answer 3 · 2017-11-09T11:22:37.120

When Using Individual Queries, you can achieve this using the following

Return sum of Weight of all parties that has roleID = 1 in PartyRole table

SELECT 
  SUM(Weight) FilteredWeigth 
  FROM dbo.Party P
  WHERE EXISTS
  (
      SELECT
      1
    FROM dbo.PartyRole PR 
      WHERE PR. PartyID = P.PartyID
    AND PR.RoleId = 1
  )

Return sum of OldWeigth of all parties that has statusID = 2

SELECT 
  SUM(OldWeigth) FilteredOldWeigth 
  FROM dbo.Party P
  WHERE EXISTS
  (
      SELECT
      1
    FROM dbo.PartyRole PR 
      WHERE PR. PartyID = P.PartyID
    AND PR.RoleId = 2
  )

Return sum of distances of all parties that has locationID = 3

SELECT 
  SUM(Distance) FilteredDistance 
  FROM dbo.PartyLocation
    WHERE LocationID = 3

Return sum of distances of all parties that has roleID = 2

SELECT SUM(Distance) FROM PartyLocation PL
  WHERE EXISTS
  (
      SELECT 1 FROM PartyRole PR
      WHERE PR.PartyID = PL.PartyID
        AND PR.Roleid = 2
   )

If you want to get the result of all these in a single result set. then maybe you can try a pivot query. Like this

WITH CTE
AS
(
SELECT 
      'FilteredWeigth' ColNm,
      SUM(Weigth) Val 
      FROM dbo.Party P
      WHERE EXISTS
      (
          SELECT
          1
        FROM dbo.PartyRole PR 
          WHERE PR. PartyID = P.PartyID
        AND PR.RoleId = 1
      )
UNION
  SELECT 
        'FilteredOldWeigth' ColNm,
  SUM(OldWeigth) Val 
      FROM dbo.Party P
      WHERE EXISTS
      (
          SELECT
          1
        FROM dbo.PartyRole PR 
          WHERE PR. PartyID = P.PartyID
        AND PR.RoleId = 2
      )
  UNION
  SELECT 
  'FilteredDistance'    ColNm,
  SUM(Distance) Val 
      FROM dbo.PartyLocation
        WHERE LocationID = 3
  UNION
  SELECT
  'AnotherFilteredDistance' ColNm,
  SUM(Distance) Val FROM PartyLocation PL
      WHERE EXISTS
      (
          SELECT 1 FROM PartyRole PR
          WHERE PR.PartyID = PL.PartyID
            AND PR.Roleid = 2
       )
)
SELECT
  *
  FROM CTE
  PIVOT
  (
      SUM(Val)
      FOR ColNm IN
      (
        [FilteredWeigth],[FilteredOldWeigth],[FilteredDistance],[AnotherFilteredDistance]
       )
  )Pvt

The Result Will be

What if a record in the Party does not have an entry in Party role or Party Location? Then we need Left or Right Join. So I thought this will suite more — Jayasurya Satheesh, Nov 09 '17 at 11:36
all your queries above in the CTE are aggregation functions doing a sum thus returning a garunteed one row whether data exists or not. joining 4 queries of 1 row earch = 1 row. Unioning just to pivot is pointless and unnecessary — Ab Bennett, Nov 09 '17 at 11:38

score 0 · Answer 4 · answered Nov 09 '17 at 11:50

I could think of only three possible options:

Union query with four different select statements as answered by @ab-bennett
Join all tables then use select statements as answered by sarslan
Mix of 1 and 2, based on experiments

Coming to the question you asked:

Can we write a query that will query each table just once?

Assuming best performance is the goal, following could happen in each of the above cases:

All select statements would have their own where clause. This would perform best when where produces few rows compared to the count(*). Note that Joins are terrible for very large tables.
A join is made once, and the desired output is obtained from the same Joined table. This would perform optimal when where produces significant number of rows and the table is not too big to join.
You can mix JOIN / IN / EXISTS / WHERE to optimize your queries based on number of rows you are having in table. This approach could be used when your dataset cardinality might not vary a lot.

I have to generate this queries from the application. So I cant calculate the number of rows to make a decision based on that. — Artashes Khachatryan, Nov 09 '17 at 11:54
Usually, one could go about estimating the `number of rows` in tables based on `number of users` of the application — vishwarajanand, Nov 09 '17 at 11:56

SQL reporting query

4 Answers4