none
Excel SQL Count getting wrong count RRS feed

  • Question

  • Hi All,

    I am using below query to count "Cust_ID" which is a column in excel. Cust_ID column has many cells that are blank. So my below query works but it also gives me count of blank cells for Cust_ID column. How shall i change the below query to only count cells that are not blank.


    strSQL = "SELECT Week AS SHIP_Date, count(Cust_ID) AS [Customer ID], Sum(TotalSales) AS [Order Amount] FROM [REP_1] GROUP BY Week"

    Also if there is a space in the name of my column header in excel like "Total Sales" than sum(total sales) in above query gives syntax error.

    Thanks,

    Zav


    • Edited by zaveri cc Wednesday, September 16, 2015 4:21 PM
    Wednesday, September 16, 2015 4:17 PM

Answers

  • Hi zaveric,

    Have your issue been resolved? If you have, it would been appreciated if you could share us your solution. If not, please feel free to let us know your current issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by zaveri cc Monday, September 28, 2015 4:03 PM
    Tuesday, September 22, 2015 6:59 AM
  • Hi zaveri,

    If your issue has not been resolved, please feel free to let us know your details.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by zaveri cc Monday, September 28, 2015 4:03 PM
    Monday, September 28, 2015 6:52 AM

All replies

  • Hi Zav,

    Where do you run your sql statement, in other application or with getting external data with from Microsoft query? I assume you that you use get external data with from Microsoft query?

    >> How shall i change the below query to only count cells that are not blank.

    I made a test as your description, but I failed to reproduce your issue. I suggest you check the datasource. It would be helpful if you could share us a demo file to reproduce your issue.

    >> if there is a space in the name of my column header in excel like "Total Sales" than sum(total sales) in above query gives syntax error.

    For a space, you could add tab “`”, a simple code like below:

    SELECT rangetest.X, Count(rangetest.Y) AS 'y', Sum(rangetest.`X Y`)
    FROM `C:\Test.xlsx`.rangetest rangetest
    GROUP BY rangetest.X

    I suggest you manually get external data with from Microsoft query, and then test the sql statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, September 17, 2015 5:38 AM
  • Data is in separate sheet in the same workbook and name range"REP_1" is defined to it. My query is doing the count of Cust_ID which has few blank cells and so even blank cells are included in the count.

    Thursday, September 17, 2015 12:42 PM
  • Hi zaveri,

    As my test, I could not reproduce your issue, and I assume it is related with your specific workbook. It would be helpful if you could share us your workbook through OneDrive.

    In addition, I think you could test with other workbook to check whether this issue could be reproduced.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 18, 2015 9:15 AM
  • Hi zaveric,

    Have your issue been resolved? If you have, it would been appreciated if you could share us your solution. If not, please feel free to let us know your current issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by zaveri cc Monday, September 28, 2015 4:03 PM
    Tuesday, September 22, 2015 6:59 AM
  • My issue in not resolved yet, i will let you know the issue in details soon.

    Thanks,

    Zaveri

    Thursday, September 24, 2015 2:38 PM
  • Hi zaveri,

    If your issue has not been resolved, please feel free to let us know your details.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by zaveri cc Monday, September 28, 2015 4:03 PM
    Monday, September 28, 2015 6:52 AM
  • Hi Edward,

    As of now i am still working on getting data into excel from data source and once done will post my issues if any.

    Thanks,

    Zaveri

    Monday, September 28, 2015 4:03 PM