SQL Count based on value in another table

user_13978390 0
Viewed 97

I need to do two counts of a column based on the content of another column in a different table.

SELECT WMTRANSACTIONHEADER.TRANSACTIONTYPE
   , IMITEM.ITEMSUPCCODE
   , COUNT(IMITEM.ITEMSUPCCODE) AS 'Shipment'
   , COUNT(IMITEM.ITEMSUPCCODE) AS 'Reciept'
   , CAST(MTRANSACTIONHEADER.TRANSACTIONDATE AS DATE) AS DATE
FROM WMTRANSACTIONHEADER 
  INNER JOIN WMTRANSACTIONDETAIL ON WMTRANSACTIONHEADER.ROWID = WMTRANSACTIONDETAIL.R_TRANSACTIONHEADER 
  INNER JOIN IMITEM ON WMTRANSACTIONDETAIL.R_ITEM = IMITEM.ROWID

Forgive me for how unreadable it is, but generally, I'm trying to count ITEMSUPCCODE based on whether TRANSACTIONTYPE is either an 'R' or an 'S'. Any tips/ideas would be great!

EDIT:

Unfortunately I underestimated this task and the importance of other fields. So let me explain this more in depth. I am making a report that counts the daily ITEMSUPCCODE fields. There are 8 ITEMSUPCCODE fields that are possible, and we want a count of each one. So the perfect report would group all the transactions in a day, then group by each entry in ITEMSUPCCODE, and then count each entry in ITEMSUPCCODE, based on what is in TRANSACTIONTYPE. I apologize for not being clear, I thought the counting part was the only issue I had!

sql-server sql
3 Answers

Something like this if I am getting your question right: Below query is not tested

With tmp AS(Select WMTRANSACTIONHEADER.TRANSACTIONTYPE, IMITEM.ITEMSUPCCODE,
CONVERT(DATETIME, CONVERT(DATE, MTRANSACTIONHEADER.TRANSACTIONDATE)) Date; 
FROM WMTRANSACTIONHEADER INNER JOIN WMTRANSACTIONDETAIL
ON WMTRANSACTIONHEADER.ROWID = WMTRANSACTIONDETAIL.R_TRANSACTIONHEADER 
INNER JOIN IMITEM ON WMTRANSACTIONDETAIL.R_ITEM = IMITEM.ROWID
WHERE TRANSACTIONTYPE in ('R', 'S'))

select count(ITEMSUPCCODE) , Date, Transactiontype
from tmp
group by Date, Transactiontype

You can change count(ITEMSUPCCODE) or count(DISTINCT ITEMSUPCCODE) based on your requirement.

user_86929570

Here's a different approach with the result in rows instead of columns.

It also allows you to count how many distinct IMITEM.ITEMSUPCCODE there are in your table

Select count(distinct IMITEM.ITEMSUPCCODE),  --count of distinct values in the group
       count(IMITEM.ITEMSUPCCODE),           --non-distinct values
       TRANSACTIONTYPE
  FROM WMTRANSACTIONHEADER 
 INNER JOIN WMTRANSACTIONDETAIL
    ON WMTRANSACTIONHEADER.ROWID = WMTRANSACTIONDETAIL.R_TRANSACTIONHEADER 
 INNER JOIN IMITEM
    ON WMTRANSACTIONDETAIL.R_ITEM = IMITEM.ROWID
 where TRANSACTIONTYPE in ('R', 'S')
 group by TRANSACTIONTYPE
user_68689270

In cases like this, I often use a subquery like this:

select 
    NameLast, 
    (select count(*) from Paychecks where Paychecks.EmployeeID = Employees.ID) As PaycheckCount  
from Employees

The inner join isn't needed for the subtable.

Reworking your original query, I think this should work:

SELECT WMTRANSACTIONHEADER.TRANSACTIONTYPE, IMITEM.ITEMSUPCCODE, COUNT(IMITEM.ITEMSUPCCODE) AS 'Shipment',
(SELECT COUNT(ITEMSUPCCODE) FROM IMITEM WHERE WMTRANSACTIONDETAIL.R_ITEM = IMITEM.ROWID) AS 'Reciept'

FROM WMTRANSACTIONHEADER INNER JOIN WMTRANSACTIONDETAIL ON 
WMTRANSACTIONHEADER.ROWID = WMTRANSACTIONDETAIL.R_TRANSACTIONHEADER

Does this work for you?

user_40170830
Related