SQL query add new custom row dynamically

Question

Below is my SQL query which currently outputs this result:

SQL query:

SELECT 
    COUNT(*) AS noofuser,
    [DateTime], [Date], [hour],
    [Company]
FROM 
    LMT2_lmutilserverLicenseuser

But, I need to add extra row called 'Total' based on a column 'DateTime' as shown in the below table.

@nikhilsugandh total of 'Count' Column based on 'DateTime' column — pankaj bawdane
– pankaj bawdane, Commented Dec 4, 2018 at 6:28
@TimBiegeleisen type of Date column is Date and 'DateTime' column is Varchar — pankaj bawdane
– pankaj bawdane, Commented Dec 4, 2018 at 6:29

peeyush singh · Accepted Answer · 2018-12-04 09:50:06Z

1

Combining the query with a group by should work, haven't been able to run but this should give the result. You can insert the result of your query in temp table (thus count(*) gets its own column) and then just do another group by on the temp table

SELECT cnt, date_time, date , hr ,company 
FROM LMT2_lmutilserverLicenseuserTemp 
UNION 
SELECT SUM(cnt), date_time, MAX(date) , MIN(hr) , 'tot' AS company 
FROM LMT2_lmutilserverLicenseuserTemp
GROUP BY date_time
ORDER BY date_time

edited Dec 4, 2018 at 9:50

answered Dec 4, 2018 at 7:38

peeyush singh

1,4071 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pankaj bawdane Over a year ago

Error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

peeyush singh Over a year ago

Yikes....yes you cannot do sum (count(*)) as the exception says, but the logic is still same, see updated ans

Collectives™ on Stack Overflow

SQL query add new custom row dynamically

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related