Ms Script to group with totals

jana
jana used Ask the Experts™
on
Hi,

Please view our script to display the Following:

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)

Open in new window


The script is not display what we need.  the script is:
SELECT Date1, tools, COUNT(*) AS "Same-Day-USe"
FROM TableName
GROUP BY tools,Date1
HAVING COUNT(*) > 1

Open in new window


Please advice
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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

Open 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

Open 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)

Open in new window

Ryan ChongSoftware Team Lead

Commented:
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

Open in new window

Software Team Lead
Commented:
for similar output, try:

	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 + ' (' + cast(COUNT(*) as varchar(3)) + ')' AS ToolUse
FROM TableName
GROUP BY tools,Date1

Open in new window


and Order By like below when necessary (but it seems that I can't get your output sorting logic...)

Order by 1, COUNT(*) desc

Open in new window

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial