CODEWITHSUNDEEP
sqlasp.net
ramzan ali
ramzan ali

Reputation: 501

Regarding SQL Query for asp.net application

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

Upvotes: 0

Views: 47

Answers (3)

Ludricio
Ludricio

Reputation: 156

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.

Upvotes: 1

Rajat Mishra
Rajat Mishra

Reputation: 3770

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

Upvotes: 0

gopi nath
gopi nath

Reputation: 81

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

Upvotes: 0

Related Questions