How do i...mutiply two(2) column of a table

Question

0.00/5 (No votes)

See more:

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:

SQL

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 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.

Posted 23-Jun-18 4:22am

Member 11307750

Updated 23-Jun-18 6:40am

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike V Baker · Answer 1 · 2018-06-23T06:42:00

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.

SQL

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

SQL

Select Count(Jobtype)*Sum(Value)

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

OriginalGriff · Answer 2 · 2018-06-23T04:51:00

Solution 1

See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Posted 23-Jun-18 4:51am

OriginalGriff