我怎么...表格的两（2）列 [英] How do i...mutiply two(2) column of a table

问题描述

请特定日期需要多次使用Jobtype和MEAL_Ticket的价值







我想要的是

Please i need to mutiply Total no of Jobtype and the Value of MEAL_Ticket for a particular date



What i want is

             Fulltime   FulltimeTicket_Value           Contract       Casual
2018/06/04         1              500(1*500)                  6            2
2018/06/05         3             1500(3*500)                  0            0
2018/06/06         0              0  (0*500)                  3            1
2018/06/07         2             1000(2*500)                  1            0
2018/06/08         1              500(1*500)                  1            3
2018/06/09         0                0(0*500)                  1            4

请帮助



我尝试过：

Please help

What I have tried:

SELECT DISTINCT Datename(dw, Time)+', '+CONVERT(VARCHAR(12), Time, 107) as Date_to_Display,Vale,
           (SELECT COUNT(*) FROM CanLog as c WHERE c.Time= clog.Time AND jobtype = 'fulltime') AS Fulltime,
    	   (Select Count(Jobtype)*Sum(Value) from	CanLog WHERE Time BETWEEN '2018-02-12' AND '2018-02-14' AND jobtype = 'fulltime' )AS FulltimeTicket_Value,
           (SELECT COUNT(*) FROM CanLog as c WHERE c.Time = clog.Time AND jobtype = 'contract') AS Contract,
           (SELECT COUNT(*) FROM CanLog as c WHERE c.Time = clog.Time AND jobtype = 'casual') AS Casual
    FROM CanLog AS clog
    WHERE Time BETWEEN '2018-02-12' AND '2018-02-14' 
    GROUP BY Time ,Jobtype 
    ORDER BY 2 ASC

i得到此错误



列'CanLog.Value'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

i got this error

Column 'CanLog.Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

推荐答案

请参见： SQL GROUP By和Column名称'在选择列表中无效，因为......错误 [ ^ ]

这不是你所描述的，但它可能足以让你弄清楚从哪里开始。

您的CONVERT函数使用107，但这不是您在文本显示中所拥有的格式。看起来你只想要'全职'类型的工作。我在列表中包含了所有的jobtype，以便更好地查看分组。该示例插入记录的日期时间值都没有时间差（所有只是日期）。如果实际记录在时间上有差异，比如它们记录的实际时间，那么它必须是一个子查询，可以在一个日期之后将所有不同的时间汇集在一起​​，然后您可以对其进行分组。

底部有两个选项，一个可以看到将在下面的'group by'选项中使用的原始记录。

This is not exactly what you're describing but it might be enough for you to figure out where to go from here.
Your CONVERT function uses 107 but that's not the format you have in your text display of what you want. It looks like you're only wanting the 'fulltime' type of job. I included all the jobtype in the listing to better see the grouping. The sample inserts records with date time values all having no time difference (all just date). If the actual records have differences in time, like the actual time when they logged, then it'll have to be a subquery to pull together all the different times under a single date upon which you can then group.
There are two selects at the bottom, one so you can see the raw records that will be used in the 'group by' select which follows.

CREATE TABLE CanLog ([Time] DateTime NOT NULL, [jobtype] nvarchar(25) not null, [value] int not null)

-- create 3000 records with random values 
-- (some fail when trying to insert NULL in jobtype, but continues until 3000 are inserted)
WHILE (SELECT COUNT(*) FROM CanLog) < 3000
BEGIN
	INSERT INTO CanLog ([Time], [jobtype], value) VALUES
	(
		DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '2018-01-01'), 
		CHOOSE((ABS(CHECKSUM(NewId())) % 3) + 1, 'fulltime', 'parttime', 'contract'),
		ABS(CHECKSUM(NewId())) % 300 + 300
	)
END

-- SELECT some records
SELECT jobtype, [Time], [Value] FROM CanLog WHERE [Time] BETWEEN '2018-02-12' AND '2018-02-14' ORDER BY jobtype, [Time]
SELECT CONVERT(VARCHAR(12), [Time], 111) AS DateToDisplay, jobtype, COUNT(*) AS JobTypeCount, SUM(value) AS Ticke_Value FROM CanLog WHERE [Time] BETWEEN '2018-02-12' AND '2018-02-14' GROUP BY [Time], jobtype

我真的对这个解决方案是否有效感兴趣你好。

哦，我差点忘了......有一点你有

I'd really be interested in whether or not this solution works for you.
Oh, I almost forgot... at one point you have

Select Count(Jobtype)*Sum(Value)

你不想这样做。 Sum已经是这些项的值的总和。如果你乘以它们那么总数就会出错。

HTH，

Mike

You don't want to do that. Sum is already the total of the values for those items. If you multiply them then the total will be wrong.
HTH,
Mike

这篇关于我怎么...表格的两（2）列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！