need a grand total of duplicates for each station

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need a grand total of duplicates for each station

Author

Topic

u82cats
Starting Member

3 Posts

Posted - 2010-08-05 : 17:12:11

I am fairly new to sql query, but was able to come up with this. I am trying to get a total number of duplicates in a table for each station. I am able to get that with this query, but it breaks it down for each station and how many times each order number was in that table (Example1). Now, I just need it to give me a grand total for each station (example2).

(Query)
SELECT empid as Station, COUNT(fullorderno) AS "Times Scanned"
From Database.dbo.[shipping.work]
where [datetime] Between '7/20/10 4:00 pM' and '7/21/10 12:00 AM'
GROUP BY empid, fullorderno
HAVING ( COUNT(fullorderno) > 1 )
order by empid asc

(example1)
Station | Times Scanned
a | 3
a | 2
b | 2
b | 2

(example2)
Station | Times Scanned
a | 5
b | 4

Any help is greatly appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-05 : 17:27:05

Drop the FullOrderNo in the GROUP BY statement?

N 56°04'39.26"
E 12°55'05.63"

ann
Posting Yak Master

220 Posts

Posted - 2010-08-05 : 18:51:19

Try this:

Select EmpID from Database.dbo.[shipping.work]
Where Exists
(
SELECT COUNT(fullorderno) AS "Times Scanned"
From Database.dbo.[shipping.work]
where [datetime] Between '7/20/10 4:00 pM' and '7/21/10 12:00 AM'
GROUP BY empid, fullorderno
HAVING ( COUNT(fullorderno) > 1 )
)

u82cats
Starting Member

3 Posts

Posted - 2010-08-10 : 10:47:34

sorry all, none of that gave me the results i needed. i took a different route and was able to get it to work. i used a temp table to gather the results and and then was able to combine them all and give the totals to what i was looking for.

Create Table #ScanCount (
Station char (2), ScanCount int )

Insert into #ScanCount (Station, ScanCount)

SELECT empid as "Station", count(fullorderno) as "ScanCount"
From Database.dbo.[shipping.work]
where [datetime] Between '7/20/10 4:00 PM' and '7/21/10 12:00 AM'
GROUP BY empid, fullorderno
HAVING ( count(fullorderno) > 1 )
order by empid asc

select Station, sum (ScanCount) as DupScan
from #ScanCount
group by Station

Drop table #ScanCount

thank you all for your help!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-11 : 14:08:55

Unless there is some hidden data of which we know nothing if, this (my previous suggestion) will render same result.

SELECT empid as Station, COUNT(fullorderno) AS "Times Scanned"
From Database.dbo.[shipping.work] 
where [datetime] Between '7/20/10 4:00 pM' and '7/21/10 12:00 AM'
GROUP BY empid , fullorderno
HAVING ( COUNT(fullorderno) > 1 )
order by empid asc

N 56°04'39.26"
E 12°55'05.63"

u82cats
Starting Member

3 Posts

Posted - 2010-08-12 : 10:29:21

by dropping the "Group BY" fullorderno, it will just give me a total of all scans for the time selected and not just the duplicates total.

with the fullorderno it returns;
Station | Times Scanned
a | 3
a | 2
b | 2
b | 2

without the fullorderno it returns;
a | 74
b | 68

and all i need it to return is;
Station | Times Scanned
a | 5
b | 4

however, with the fullorderno being text a SUM command will not work, but if you have any other ideas i am open to them!

here is a sample of the table data to see what i am working with;
ID Empid fullorderno edpno ss action datetime
54777 c A24622630101 14951 0 14 7/27/2010 1:39:23 PM
54778 c A24605360101 31531 0 14 7/27/2010 1:40:08 PM
54779 c A24613680101 14888 0 14 7/27/2010 1:42:26 PM
54780 c P01837420101 11263 0 14 7/27/2010 1:43:04 PM
54781 c A24607720101 14883 0 14 7/27/2010 1:44:00 PM
54782 c A24609070101 13772 0 14 7/27/2010 1:44:21 PM
54783 c A24617190101 15562 0 14 7/27/2010 1:45:15 PM
54784 c B10877270101 19824 0 14 7/27/2010 1:45:55 PM
54785 a P01836650101 9410 0 14 7/27/2010 1:46:36 PM
54786 c A24610040101 22929 0 14 7/27/2010 1:46:50 PM
54787 b B10873940102 434 0 14 7/27/2010 1:47:03 PM
54788 c A24610040101 14940 0 14 7/27/2010 1:47:28 PM
54789 a A24597760101 5644 0 14 7/27/2010 1:47:54 PM
54790 c A24607700101 14887 0 14 7/27/2010 1:48:08 PM
54791 a A24597760101 15618 0 14 7/27/2010 1:48:43 PM
54792 c B10878530101 1681 0 14 7/27/2010 1:48:50 PM
54793 b B10873940102 434 0 14 7/27/2010 1:49:06 PM
54794 c B10877480101 22929 0 14 7/27/2010 1:49:48 PM
54795 a B10878170101 22929 0 14 7/27/2010 1:50:36 PM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources