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

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
groovymonkeyAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
jimyXCommented:
This should work:
SELECT ssn, lp, lpt1count, lpt2count
FROM t1
GROUP BY ssn,  lp, lpt1count, lpt2count;
0
 
TommySzalapskiCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SharathData EngineerCommented:
What is your expected result for the sample provided.
0
 
groovymonkeyAuthor Commented:
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
 
groovymonkeyAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
groovymonkeyAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
groovymonkeyAuthor Commented:
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
 
groovymonkeyAuthor Commented:
0
 
SharathData EngineerCommented:
Could you please provide some sample data with expected result?
0
 
groovymonkeyAuthor Commented:
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
 
groovymonkeyAuthor Commented:
You are a genius!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.