0

How to use DCOUNT on select- sql query while using group by?

I have a problem and first, I want to show part of the real table ![I WANT a query that show me per date the amount of bl_no in feedback_status=”99” , the amount of bl_no is feedback_status=”1”

I try to wrote this code in sql query on MS ACCESS and I don’t get the result that I want

SELECT CUSTOM_TRANSMISSION_DATE, dcount("*","M_MAAGAR_BL_CUSTOM_TRANSMISSION_LOG","FEEDBACK_STATUS=0") 
FROM M_MAAGAR_BL_CUSTOM_TRANSMISSION_LOG
GROUP BY CUSTOM_TRANSMISSION_DATE;

Can you help me?

Image

Suvasis
  • 1,451
  • 4
  • 24
  • 42
Liran Kremer
  • 51
  • 4
  • Your query seems fine, but I think you need to add a few extra conditions. You should also add a `WHERE clause` with the feedback status you want displayed (I believe it's 99 in your case) like `WHERE feedback_status="99"` – Radu Gheorghiu Oct 15 '13 at 08:11
  • Ideally you would take some more time to format your question as it is a bit hard to understand the big picture of the question. Additional details are always welcome, as example data and expected output of your query are most of the time required. – Radu Gheorghiu Oct 15 '13 at 08:13

1 Answers1

0

Your question is a little confusing, but I think you are making it more complicated than it needs to be. The following query will give you the number of BL_NO having FEEDBACK_STATUS = '99' for each unique date. I'm not sure why you have FEEDBACK_STATUS=0 in your query. You might have to clarify the question if this doesn't work for you.

SELECT CUSTOM_TRANSMISSION_DATE, COUNT(BL_NO) as Amount 
FROM M_MAAGAR_BL_CUSTOM_TRANSMISSION_LOG
WHERE FEEDBACK_STATUS=99
GROUP BY CUSTOM_TRANSMISSION_DATE;
user3646932
  • 486
  • 4
  • 12