CODEWITHSUNDEEP
sqlsql-servert-sql
lingers
lingers

Reputation: 11

Multiply two (2) columns of a table

Please I need to multiply total no of jobtype and the value of MEAL_Ticket for a particular date

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

What I want is this output:

           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

EDIT:

Sample table

CREATE TABLE [dbo].[CanLog]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](50) NOT NULL,
    [Designation] [nvarchar](50) NOT NULL,
    [Jobtype] [nvarchar](50) NOT NULL,
    [Employ_No] [nvarchar](50) NOT NULL,
    [Receipt_No] [int] NOT NULL,
    [Username] [nvarchar](50) NOT NULL,
    [Time] [datetime] NOT NULL, 
    [Value] [int] NULL
) ON [PRIMARY]

Upvotes: 0

Views: 116

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

Initial query has a lot of warning signs:

  • GROUP BY does not match SELECT
  • DISTINCT + GROUP BY
  • unnecessary subqueries could be replaced with conditional aggregation
  • positional ORDER BY
  • I would recommend to avoid column names such as Value/Time
  • Datename(dw, Time) + ', ' + CONVERT(VARCHAR(12), Time, 107) I would replace it with CAST(Time AS DATE)
  • Time BETWEEN '2018-02-12' AND '2018-02-14' this kind of comparison may be dangerous (as mentioned by @Gordon)

I believe you want something like:

SELECT 
    Date_to_Display = CAST(Time AS DATE),
    Fulltime = SUM(CASE WHEN jobtype = 'fulltime' THEN 1  ELSE 0 END),
    FulltimeTicket_Value = SUM(CASE WHEN jobtype = 'fulltime' THEN 1 ELSE 0 END) *
                               MAX(CASE WHEN jobtype = 'fulltime' THEN Value ELSE 0 END),
    Contract = SUM(CASE WHEN jobtype = 'contract' THEN 1 ELSE 0 END),
    Casual = SUM(CASE WHEN jobtype = 'casual' THEN 1 ELSE 0 END) 
FROM 
    CanLog AS clog
WHERE 
    Time >= '2018-02-12' and time < '2018-02-15' 
GROUP BY 
    CAST(Time AS DATE)
ORDER BY 
    CAST(Time AS DATE);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

In addition to many of the points that Lukasz makes, you should avoid using between on date/time columns. A very good explanation is in Aaron Bertrand's blog What do Between and the Devil Have In Common.

You can also simplify the logic using indicator variables:

select cast(time as date) as Date_to_Display,
       sum(is_fulltime) as Fulltime,
       sum(value * is_fulltime) as FulltimeTicket_Value,
       sum(is_contract) as Contract,
       sum(is_casual) as Casual
from CanLog cl cross apply
     (values (case when cl.jobtype = 'fulltime' then 1 else 0 end),
             (case when cl.jobtype = 'contract' then 1 else 0 end),
             (case when cl.jobtype = 'casual' then 1 else 0 end)
     ) v(is_contract, iscasual)
where Time >= '2018-02-12' and time < '2018-02-15' 
group by cast(time as date) 
order by cast(time as date);

Upvotes: 1

Related Questions