You must Sign In to post a response.
  • Category: SQL Server

    SQL Query for getiing Total Count

    Hi .......

    I have a table Structure like this as shown below, The table is Customer . In that i Have 3 coulumns

    Customer ID Item ID Status
    C1 A New
    C2 B New
    C1 C Rejected
    C1 D Approved
    C2 A New

    Here I want to get result as

    Status Customer Count Item Count
    New 3 3
    Approved 1 1
    Rejected 1 1


    and
    Total Customers - 2

    Total Items - 4

    Total New Status - 2 customers (c1 and C2)

    Total Approved status - 1
    Like that . Please provide me the SQL queries which help me in this

    Thanks and Regards
    Sankar
    Cochin
  • #762388
    Hi Sankar,

    Please check below code snippet to find your desired results:

    --Total Customers
    SELECT COUNT(CustomerID) AS TotalCustomer FROM (SELECT DISTINCT CustomerID FROM Customer)A

    --Total Items
    SELECT COUNT(Item) AS TotalItem FROM (SELECT DISTINCT Item FROM Customer)A

    --Total New Status
    SELECT COUNT(CustomerID) AS TotalNewStatus FROM (SELECT DISTINCT CustomerID FROM Customer WHERE (IDStatus = 'New'))A

    --Total Approed Status
    SELECT COUNT(CustomerID) AS TotalApprovedStatus FROM (SELECT DISTINCT CustomerID FROM Customer WHERE (IDStatus = 'Approved'))A

    Hope this will be helpful to you.

    Thanks,
    Ram Prasad

  • #762390
    Hi

    This query works

    SELECT

    COUNT(distinct(custid)) as totalcustomers,

    COUNT(distinct(itemid))as totalitems,

    (select COUNT((status))
    from customer where status='new')as newstatus,

    (select COUNT((status))
    from customer where status='rejected')as rejectedstatus,

    (select COUNT((status))
    from customer where status='approved')as approvedstatus

    FROM customer


    find the attachment of output.

    Regards

    Sridhar Thota.

    Sridhar Thota.
    Editor: DNS Forum.

    Delete Attachment

  • #762395
    Here I want to get result as

    Status Customer Count Item Count
    New 3 3
    Approved 1 1
    Rejected 1 1

  • #762396
    Hi Sankar,

    Please try below query:

    SELECT IDStatus AS Status, COUNT(CustomerID) AS CustomerCount, COUNT(Item) AS ItemCount
    FROM Customer
    GROUP BY IDStatus
    ORDER BY Status

    For your better understanding, please find attached screenshot.

    Hope this will be helpful to you.

    Thanks,
    Ram Prasad

    Delete Attachment


Sign In to post your comments