SQL Query以获取所需的详细信息 [英] SQL Query to get required details

问题描述

我的表格是这样的：

my table is like this:

SlNo    Date                 Session    MemberCode  Litres  Fat     Price   Amount  GS     Col1   Col
16   2013-07-02 16:27:17.000    E           0        1.00   0.00    28.00   28.00   NULL    S     NULL
17   2013-07-08 16:55:26.000    E           1        38.00  3.80    17.56   667.28  4.00    P     NULL
18   2013-07-08 22:10:03.000    E           0        0.00   3.90    17.63   0.00    0.00    GT     -
19   2013-07-08 23:03:08.000    E           2        2.00   3.50    17.50   35.00   4.00    P      -
20   2013-07-08 23:03:22.000    E           13       3.90   3.80    17.56   68.48   4.00

我想要一个查询来获得结果

I want a query to get the result as

Date, 
Session, 
Total Members for session, 
Total Litres for Session, 
Total Amount For the Session,  
Fat(Where Col1='GT') as GTFat, 
Price(Where Col1='GT') as GTPrice, 
NetAmount as GTPrice * (TotalLitres for Session), 
Profit or Loss(NetAmount-Total Amount for the Session)

这是这样的事情

This is something like this

Select Date,Session,Count(MemberCode),Sum(Litres),Sum(Amount),Fat as GTFat, Price as GTPrice ( this is for the Date and session which u have selected before where it will be a single row for that particular date/session and you will get multiple rows for Litres and Fat and ....), Sum(Litres)*GTPrice as GTAmount, GTAmount-Sum(Amount) as PnL
from myTable
where Date ='2013-07-08'
Group by Date, Session 

我很困惑请帮帮我



提前致谢

I am confused Please help me

Thanks in advance

推荐答案

测试一下：

Test it:

DECLARE @tmp TABLE(SlNo INT, Date DATETIME, Session VARCHAR(30), MemberCode INT,
		Litres DECIMAL(8,2),  Fat DECIMAL(8,2), Price DECIMAL(8,2), Amount DECIMAL(8,2),  GS DECIMAL(8,2))

INSERT INTO @tmp (SlNo, Date, Session, MemberCode, Litres, Fat, Price, Amount, GS)
SELECT 16 AS SlNo, '2013-07-02 16:27:17.000' AS Date, 'E' AS Session, 0 AS MemberCode, 1.00 As Liters, 0.00 AS Fat, 28.00 AS Price, 28.00 AS Amount, NULL AS GS 
UNION ALL SELECT 17, '2013-07-08 16:55:26.000', 'E', 1, 38.00, 3.80, 17.56, 667.28, 4.00
UNION ALL SELECT 18, '2013-07-08 22:10:03.000', 'E', 0, 0.00, 3.90, 17.63, 0.00, 0.00
UNION ALL SELECT 19, '2013-07-08 23:03:08.000', 'E', 2, 2.00, 3.50, 17.50, 35.00, 4.00
UNION ALL SELECT 20, '2013-07-08 23:03:22.000', 'E', 13, 3.90, 3.80, 17.56, 68.48, 4.00



SELECT CONVERT(DATETIME,CONVERT(VARCHAR(30),Date,112)) AS Date, Session, COUNT(MemberCode) AS GTMemberCode, 
	SUM(Litres) AS GTLiters, SUM(Fat) AS GTFat, SUM(Price) AS GTPrice, SUM(Litres) * SUM(Amount) AS GTAmount, 
	SUM(Litres) * SUM(Amount)-Sum(Amount) as PnL
FROM @tmp
GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR(30),Date,112)), Session

这篇关于SQL Query以获取所需的详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！