Regarding SQL Query for asp.net application

Question

I am new in SQL as well as ASP.NET. For one of my projects I need the following output requried output. So far I have done this my current output. But here some Test Type Name is repeating. How to solve this problem?

My current query:

SELECT 
    tyi.TestType, 
    COUNT(tr.TestID) AS NoOfTest,
    (Fee*COUNT(tr.TestID)) AS TotalAmount,
FROM
    TestTypeInfo AS tyi 
        LEFT OUTER JOIN TestInfo AS ti ON
             tyi.TestTypeId=ti.TestTypeId
        LEFT OUTER JOIN TestRequest tr ON
             ti.TestId=tr.TestId
WHERE
    EntryDate BETWEEN '2016-12-31' AND '2016-12-31' OR
    EntryDate IS NULL
GROUP BY
    tyi.TestType

Which gives me the output:

TestType      | NoOfTest | TotalAmount
---------------------------------------
ECG           | 4        | 600
X-Ray         | 4        | 800
Blood         | 6        | 1800
X-Ray         | 4        | 1200
Echo          | 4        | 4000
X-Ray         | 3        | 3300
Echo          | 0        | 0
UltraSonogram | 0        | 0

As per your query , you have done group by testtype and fee. You are getting different row for Xray because there would 3 different fees for Xray and thats evident from the total fees column. If you remove the fee column from select n group by clause , you would be getting single row for the testtypes — Rajat Mishra
– Rajat Mishra, Commented Jan 8, 2017 at 17:36
@Rajat Mishra, but then how can I get the Total Amount for a particular Test Type? — ramzan ali
– ramzan ali, Commented Jan 8, 2017 at 17:48
You could aggregate fee, would sum(Fee) AS TotalAmount help you? Hard to say without viewing the raw data. — Ludricio
– Ludricio, Commented Jan 8, 2017 at 18:01
@MYGz Haha, thanks! I simply hate reading badly structured code, as well as not being able to just copy it. Code should be placed within a code block and structured for easy reading IMHO :) — Ludricio
– Ludricio, Commented Jan 8, 2017 at 18:35

Ludricio · Accepted Answer · 2017-01-08 19:02:55Z

1

The reason why you get multiple posts for each TestType is because you use Fee in group by. This leads to that if Fee would be different in any one row, it would cause that row to post as a separate result row.

If I understand correctly (hard to say without viewing the raw data) you want to show how many tests, and the fee for all these tests, for say Xray.

Would not following suffice?

SELECT 
    tyi.TestType, 
    COUNT(tr.TestID) AS NoOfTest,
    sum(Fee) as TotalAmount,
FROM
    TestTypeInfo AS tyi 
    LEFT OUTER JOIN TestInfo AS ti ON
        tyi.TestTypeId=ti.TestTypeId
    LEFT OUTER JOIN TestRequest tr ON 
        ti.TestId=tr.TestId
WHERE
    EntryDate BETWEEN '2016-12-31' AND '2016-12-31' OR
    EntryDate IS NULL
GROUP BY
    tyi.TestType

This of course depends on how TotalAmount should be calculated.

edited Jan 8, 2017 at 19:02

answered Jan 8, 2017 at 18:09

Ludricio

15617 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ramzan ali Over a year ago

Thanks a lot, your ans was good. But there are some problem (1.) For TestType with No NoOfTest the TotalAmount is also showing instead of showing zero. (2.) Some TotalAmount is not correct. I am trying to add the new screenshot

Ludricio Over a year ago

As i stated, it is very hard to know how the computed columns should be created, since we don't know how the raw data looks, and how the output is expected to look. It would be a lot easier to give a good example if you could pull out some example data, and how you would like that example data to be look when run through the query.

Ludricio Over a year ago

I've also edited my answer, since I missed a Left outer join.

Rajat Mishra · Accepted Answer · 2017-01-08 18:49:41Z

0

Some thing like this should help you. Please check the column names once.

Select a.TestType , Sum(a.NoOfTest) , Sum(a.TotalAmount) from 
(Select tyi.TestType As TestType , Count(tr.testId) As NoOfTest, 
(Fee*Count(tr.testId)) As TotalAmount 
From TestTypeInfo As tyi 
Left Outer Join TestInfo As ti on tyi.TestTypeId = ti.TestTypeId 
Left Outer Join TestRequest tr on ti.TestId = tr.TestId 
Where EntryDate between '2016-12-31' and '2016-12-31' 
or EntryDate is null group by tyi.TestType, Fee) a 
Group by a.TestType

edited Jan 8, 2017 at 18:49

answered Jan 8, 2017 at 18:12

Rajat Mishra

3,7904 gold badges32 silver badges44 bronze badges

Comments

gopi nath · Accepted Answer · 2017-01-08 18:04:15Z

0

You can remove the fee column from main query . Then use the correlated sub query for total amount with join of fee id.

answered Jan 8, 2017 at 18:04

gopi nath

813 bronze badges

Collectives™ on Stack Overflow

Regarding SQL Query for asp.net application

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related