Solved

How do I group this query by a field (ssn) from t1

Posted on 2011-03-19
15
286 Views
Last Modified: 2012-05-11
Okay so all is working great in the db...I know want to group my results by ssn (t1)

so that I have something like this

ssn               lp             lpt1count                       lpt2count
1244          aaa222            2                                  4
1546          aaa222            1                                   4
5896          aaa222             1                                   4
1244          aaa333             2                                   2
1789          ddd123             2                                   5
0
Comment
Question by:groovymonkey
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35172649
This should work:
SELECT ssn, lp, lpt1count, lpt2count
FROM t1
GROUP BY ssn,  lp, lpt1count, lpt2count;
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35173744
Do you mean you want to sum all the counts from your previous query by the ssns?
If so try
SELECT t3.ssn, SUM(t3,lpt1count) AS lpt1Total, SUM(t3.lpt2count) AS lpt2Total
FROM (SELECT ... your original query goes here ) t3
GROUP BY t3.ssn

If you just mean you want it to so the matching ssns next to each other add
ORDER BY ssn
to the end of your old query and ignore the first half of this post.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35174038
What is your expected result for the sample provided.
0
 

Author Comment

by:groovymonkey
ID: 35180678
Alright...so maybe this will simplify what I am trying to do....I am going to use 1 table (t2) and the fields I am working with are itemId and cashierId

what I want is to output...for all items that have been sold two or more items by a specific cashier with the number that cashier has sold compared to the total number of that item sold by all cahiers...grouped by itemId...the end being that only items sold 2 or more times by the same cashier will show up (mind you mulitple cashiers may have sold the same item 2 or more times)

casierId       itemId            CountperCashier         CountAllCashiers    
bobby123    apple244            2                                  6
sue123        apple244            2                                  6
sue123        fig255                 2                                  6
mat555        fig255                 2                                  6
0
 

Author Comment

by:groovymonkey
ID: 35180706
I am using this to get duplicates...but need to figure out how to get the counts as shown above


SELECT t2.ItemId, t2.CashierId
FROM t2
WHERE (((t2.ItemId) In (SELECT [ItemId] FROM [t2] As Tmp GROUP BY [ItemId] HAVING Count(*)>1 )))
ORDER BY t2.ItemId;
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35181925
Try
SELECT t2.ItemId, t2.CashierId, Count(t2.ItemId) AS CountPerCashier
FROM t2
WHERE
GROUP BY t2.ItemId, t2.CashierId;

It will give counts of 1 where applicable, but isn't that a good thing?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35181966
To get the total count for that item too, you could do something like this.
SELECT t2.ItemId, t2.CashierId, Count(t2.ItemId) AS CountPerCashier, t3.CountAllCashiers
FROM t2 JOIN (SELECT t2.ItemId, Count(t2.ItemId) AS CountAllCashiers FROM t2 GROUP BY t2.ItemID) t3 ON t3.ItemId = t2.ItemID
WHERE
GROUP BY t2.ItemId, t2.CashierId, t3.CountAllCashiers;

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:groovymonkey
ID: 35182797
Actually the whole point of the query is to find duplicates per cashier based on Itemid so I do not want counts of 1 (I am dealing with 90,000 records and want to eliminate those)...

I have this working....but I need to do a count for all cashiers ItemId and I am not sure how to work this in to the following

SELECT t2_small.ItemId, t2_small.CashierId, Count(t2_small.ItemId) AS CountPerCashier
FROM t2_small
GROUP BY t2_small.ItemId, t2_small.CashierId
HAVING (((Count(t2_small.ItemId))>1));
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35182899
Did you try this?
SELECT t2.ItemId, 
         t2.CashierId 
    FROM t2 
   WHERE (((t2.ItemId) IN (  SELECT [ItemId] 
                               FROM [t2] AS Tmp 
                           GROUP BY [ItemId] 
                             HAVING COUNT(* ) > 1))) 
GROUP BY t2.ItemId, 
         t2.CashierId 
ORDER BY t2.ItemId;

Open in new window

0
 

Author Comment

by:groovymonkey
ID: 35183242
Okay so for this table I have three fields

lp (is the actual item e.g. apples)
ItemId (is the item and the CashierId e.g. apples bobby123
CasheirId (is the employe id e.g. bobby123)

I grouped the cashierid with the item (lp) as this was the only way I could figure out a way to group those dup items to that specific employee...

Now the only thing I cannot get to work is the count of the lp so that I can compare how many e.g. apples that employee sold compared to all of the apples sold etc etc for each item for which that emplyee has sold 2 or more of...

I have the following sort of working but the (t2_small.lp) AS CountPerAllCashiers returns the same value as the CountperCashier...

where I need it to look at the lp value associated to that ItemId and count the number of times it is in the entire db

to get something outputted like this:
so the CountofAllCashiers would add up all of the apples sold (for the first record)

itemId                             CountperCashier         CountAllCashiers (count of lp)  
bobby123 apples              2                                  6
sue123 apples                  3                                  6
sue123 figs                       2                                  6
mat555 figs                      2                                  6
0
 

Author Comment

by:groovymonkey
ID: 35183261
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35183440
Could you please provide some sample data with expected result?
0
 

Author Comment

by:groovymonkey
ID: 35183559
Sample data

lp                            ItemId                        CashierId
APPLES                          APPLES ANNIE123                ANNIE123
APPLES                          APPLES ANNIE123                ANNIE123
APPLES                          APPLES STEVE434      STEVE434
PEARS                            PEARS ANNIE123      ANNIE123
PEARS                            PEARS ANNIE123      ANNIE123
PEARS                           PEARS BOBBY555      BOBBY555
PEARS                           PEARS BOBBY555      BOBBY555
PEARS                           PEARS BOBBY555      BOBBY555
PINEAPPLE      PINEAPPLE BOBBY555      BOBBY555
PINEAPPLE      PINEAPPLE STEVE434      STEVE434

Outcome that I want
ItemId                              CountPerCashier      CountPerAllCashiers
APPLES ANNIE123                                       2      3
PEARS ANNIE123                                       2      5
PEARS BOBBY555                                       3      5

Thanks for all of your help...very much appreciated
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35183706
try this query. replace Table1 with your actual table name.
SELECT t1.ItemID, 
       t1.CashierID, 
       t1.CountPerCashier, 
       t2.CountPerAllCashiers 
  FROM ((  SELECT ItemID, 
                  CashierID, 
                  COUNT(* ) AS CountPerCashier, 
                  MAX(lp)   AS Max_lp 
             FROM Table1 
         GROUP BY ItemID, 
                  CashierID 
           HAVING COUNT(* ) > 1) AS t1 
        INNER JOIN (  SELECT lp, 
                             COUNT(* ) AS CountPerAllCashiers 
                        FROM Table1 
                    GROUP BY lp) AS t2 
          ON t1.Max_lp = t2.lp)

Open in new window

0
 

Author Comment

by:groovymonkey
ID: 35183789
You are a genius!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now