Ms Script to group with totals

Here is a data to test our script (virtual table)):

	With TableName(Tools,Date1,Time1,Tech)
		AS (select 'Hammer','2017-01-03', '12:00:43', 'Joe' union all
		    select 'Hammer','2017-01-03', '12:15:00', 'Dereck' union all
		    select 'Hammer','2017-01-03', '12:17:00', 'Lillian' union all
		    select 'Desk','2017-01-03', '10:27:01', 'Sandy' union all
		    select 'Desk','2017-01-03', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-05-10', '10:00:00', 'Joe' union all
		    select 'Desk','2017-10-02', '11:00:00', 'Joe' union all
		    select 'Hammer','2017-10-02', '14:27:00', 'Smith'  union all
		    select 'Hammer','2017-10-02', '16:00:00', 'Smith')
SELECT Date1, tools, COUNT(*) AS "Same-Day-Use"
FROM TableName
GROUP BY tools,Date1
HAVING COUNT(*) > 1

Select allOpen in new window

When running the result is:

Date1	tools	Same-Day--Use
2017-01-03	Desk	2
2017-01-03	Hammer	3
2017-10-02	Hammer	2

Select allOpen in new window

When it should display,

2017-01-03, Hammer (3)
2017-01-03, Desk (2)
2017-05-10, Hammer (1)
2017-10-02, Desk (1)
2017-10-02, Hammer (2)

Select allOpen in new window

the issue is at:

SELECT Date1, tools, COUNT(*) AS "Same-Day-Use"
FROM TableName
GROUP BY tools,Date1
HAVING COUNT(*) > 1

hence, try this instead:

SELECT Date1, tools, COUNT(*) AS "Same-Day-Use"
FROM TableName
GROUP BY tools,Date1

Select allOpen in new window

I think your ORDER is ok, seem maybe our mistake.

Worked!

So to understand the script, it would sort & group by tools/Date1, then the ' (' + cast(COUNT(*) as varchar(3)) + ')'  would sum up by Tools.

Got it.