CODEWITHSUNDEEP
mysqlcountdistinct
MrsAdmin
MrsAdmin

Reputation: 548

MySQL: Add a "Total" Count to the last row of SQL Query for Unique value only

Info: Server version: 5.1.39 / MySQL / phpMyAdmin
Php: 5.4
Server: Apache
Code is run via: Server SQL Query (copy & paste in the phpMyAdmin) or in MySQL Workbench or using a custom shopping cart manager.
Exports to: Excel (.csv then to .xlsx for sales reports)
Other: I do use a number of tables for referencing

Question
I want to add a sub-total to the bottom (or top) row of my SQL Query. I am wanting to count Unique Order numbers only. Either as a whole, or by my date query.

This works, but puts it in 1 row, 1 column then does not generate the rest of my query.

COUNT( DISTINCT( T5.orders_id ) ) As OrdUnique,

Returns: 

OrdUnique  |  OrdID  |  ProdName  |  etc  
2342       |  21     |  Name      |  Rest of data

What I would like is:

OrdID  |  ProdName  |  Qty  | etc
2525   |    prod    |  1    |  
2538   |    prod    |  1    |  
2553   |    prod    |  1    |  
2553   |    prod    |  1    |  
2538   |    prod    |  1    |  

OrdUnq = 3

The basic structure of my existing code is:

Select
T5.orders_id As OrdID,
T3.products_name As ProdName,   
T2.products_quantity As Qty,  

more content  
even more content (about 70 lines of query)  
ends with (similar)  

From /*PREFIX*/products T1
Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
Where (T5.date_purchased >= 20120101) And (T5.date_purchased <= 20131216) And T5.orders_status = x 
Order By T5.orders_id

Notes: I do not run this via PHP, it is simply a copy & paste from my .sql/.txt file in to the backend of my server OR through MySQL Workbench

Throws a table access error
(select COUNT( DISTINCT( T5.orders_id ) ) from T5.orders) As OrdUnique,

Throws a Error Code: 1242: subquery returns more than one row
(select COUNT( DISTINCT( T5.orders_id ) ) as OrdUnq FROM orders GROUP BY orders_id WITH ROLLUP),
(as seen here: Calculate the total time duration on last row in mysql)

This also does not work:
Count unique records in database

Thank you in advance for your insight.

Upvotes: 0

Views: 2413

Answers (1)

Oscar P&#233;rez
Oscar P&#233;rez

Reputation: 4397

I know it is not very efficient, but an easy solution woudl be to use this query:

    SELECT null as total,
           T5.orders_id As OrdID,
           T3.products_name As ProdName,   
           T2.products_quantity As Qty
      From /*PREFIX*/products T1
 Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
 Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
     Where (T5.date_purchased >= 20120101) 
       And (T5.date_purchased <= 20131216) 
       And T5.orders_status = 'x' 
  Order By T5.orders_id
     UNION
    SELECT count(*) AS total,
           null As OrdID,
           null As ProdName,   
           null As Qty
      FROM (select T5.orders_id
              From /*PREFIX*/products T1
         Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
        Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
         Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
             Where (T5.date_purchased >= 20120101) 
               And (T5.date_purchased <= 20131216) 
               And T5.orders_status = 'x' 
          GROUP BY T5.orders_id
          ) as s

Pay attention to UNION and GROUP BY.

Upvotes: 1

Related Questions