SQLTeam.com | Weblogs | Forums

How can we group this query only by USER?


#1

Below query is simple. 3 columns (username, quantity and time)
How can we group this query by USERNAME? It doesn't let me do it. Throws an error saying invalid expression on select list. Help Appreciated

SELECT
sysuser.username as USERNAME, sum(woitem.qtytarget) AS woitemtotal, customdecimal .info as timetaken

FROM mo
INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
left join customdecimal on customdecimal .customfieldid = 15 and customdecimal .recordid = mo.id
WHERE cast(wo.datestarted as date) = 'today'
group by sysuser.username


Group by doesnt work
#2

Are you trying to use sys.sysusers? If so you need to use specify it as sys.sysuers; i.e, the schema sys is required.

Also, sys.sysusers is a deprecated feature. You should use the sys.database_principals catalog view instead.

If the SYSUSER is a user table, then the GROUP BY should work. You would need to include customdecimal.info also into the group by clause. Alternatively, wrap the customdecimal .info column in an aggregate function - e.g.

SELECT 
sysuser.username as USERNAME, 
sum(woitem.qtytarget) AS woitemtotal, 
MAX(customdecimal.info) as timetaken

#3

Dear James,

I tried that. It gives only the maximum value. if there are 4 different time slots for user1 (user1 = 1 hour, user1 = 2 hours, user1 = 3 hours) then, Max(customdecimal.info) as timetaken gives result set as (user1 : 3). It should give user1 = 6.


#4

For reference, it looks like this thread is continued from this.


#5

Thats correct. I made small change in the query, so I thought Ill start a new one instead of confusing buddies here.


#6

Instead of min or max, can we sum up all values?


#7

If the column - in your case customdecimal.info - is of a data type that can be summed - for example INT, FLOAT etc., you can use the SUM function instead of MAX to get the SUM. However, if it is of a data type that cannot be aggregated - for example DATETIME - you cannot do that. If that is the case, what does SUM mean?


#8

Dear James, in this case, sum means adding time slots of particular user. Below code gives perfect result of all time slots. When I remove wo.num from select and groupby and try to group by user, then since I am using distinct, duplicated timeslots are not adding up.(user1 - 2hrs for wo.num 1, user1 -2 hrs for wo.num 2, user1 - 3 hours for wo.num 3, result set user1 - 3 hours). if I don't use distinct, sum is wrong. Below is the modified query. Easy to understand. I have exported all the required tables - database in excel. How do I share?

SELECT
sysuser.username as USERNAME, sum(woitem.qtytarget) AS woitemtotal, wo.num,
sum(distinct customdecimal.info) as timetaken

FROM mo
INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
left join customdecimal on customdecimal .customfieldid = 15 and customdecimal .recordid = mo.id
WHERE cast(wo.datestarted as date) = '2015-10-21'
group by sysuser.username, wo.num


#9

I don't quite understand the logic or why it is not working correctly if you remove DISTINCT. Usually people don't like to download Excel sheets from web to their computers for obvious reasons. The ideal way to post would be to provide table DDL and sample data in a consumable format. For example, see here.

The following may not be the most efficient way to do this, but you can wrap your query which groups by wo.num in an outer query which then sums on USERNAME only like shown below

SELECT
	USERNAME,
	SUM(woitemtotal) AS woitemtotal,
	SUM(timetaken) AS timetaken
FROM
(
	-- This is your query that works with wo.num grouped.
	SELECT 
	sysuser.username as USERNAME, sum(woitem.qtytarget) AS woitemtotal, wo.num,
	sum(distinct customdecimal.info) as timetaken

	FROM mo
	INNER JOIN moitem ON mo.id = moitem.moid
	LEFT JOIN wo ON moitem.id = wo.moitemid
	LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
	LEFT JOIN SYSUSER ON mo.userid = sysuser.id
	left join customdecimal on customdecimal .customfieldid = 15 and customdecimal .recordid = mo.id
	WHERE cast(wo.datestarted as date) = '2015-10-21'
	group by sysuser.username, wo.num
	----------------------------------------------------
) s
GROUP BY USERNAME;

#10

Dear James, I really dont know how to follow that as I donot know what datatypes, I am querying from an application connected to database. It does only extraction and I have schema to know where I am pulling from. And you know what, wrapping query worked like magic. Thanks so much.