Databases
StackOverflow2013
Postsdbo
POGetting the count() of a column from a resulting query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting the count() of a column from a resulting query?
    text
    copied!<p>I have a problem where when getting the results of a query on the product_view table I would also like to get the total number of product months (from the product_month column) for each month related in the query results.</p> <h2>For example</h2> <p>If I did a query like this:</p> <pre><code>SELECT * FROM product_view WHERE product_price BETWEEN 945 AND 980; </code></pre> <p>and the product_view table had the data like this:</p> <pre><code>INSERT INTO product_view (`product_id`, `product_month`, `product_price`) VALUES (4, 'DEC', 999), (4, 'FEB', 905), (4, 'JAN', 905), (8, 'DEC', 1049), (8, 'FEB', 955), (8, 'JAN', 955), (8, 'DEC', 1049), (8, 'FEB', 955), (8, 'JAN', 955), (22, 'APR', 925), (22, 'AUG', 969), (22, 'JUL', 969), (22, 'JUN', 945), (22, 'MAR', 925), (22, 'MAY', 925), (22, 'OCT', 915), (22, 'SEP', 945), (27, 'APR', 1435), (27, 'JUN', 1459), (27, 'MAY', 1435), (27, 'OCT', 1459), (27, 'SEP', 1459), (31, 'APR', 3029), (31, 'AUG', 3029), (31, 'FEB', 3029); </code></pre> <p>I would not only want the statement to give me the results of the query, but also the total count() for each month in the results returned. i.e. using the query above - FEB = 2, JUN = 1 etc...</p> <p><strong>End result - with duplicates count() results</strong></p> <pre><code>PRODUCT_ID PRODUCT_MONTH PRODUCT_PRICE Month_count 8 FEB 955 2 8 JAN 955 2 8 FEB 955 2 8 JAN 955 2 22 AUG 969 1 22 JUL 969 1 22 JUN 945 1 22 SEP 945 1 </code></pre> <p><br/></p> <p>Please could someone advise me on the best way to get the count() from the results and also is there a better way of showing these mulitple counts <strong>without the results of it being duplicated on each row in the query's result set</strong>. i.e. variables, or temporary tables (though with temp tables there are many users executing this query simultaneously, so would there be problems with getting accurate results per user as the product_price BETWEEN values of the above query can be changes by the user)</p> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload