Help with multiplying count with column

[bensonsearch]

Hi All,

I have a need to pull data from one table where 2 colunms need to be multiplied. more precisely a count of 1 colunm and a plain colunm.

 

I have tried many different ways this was the last one i tried:

 

SELECT des,COUNT(des),SUM(price),(qty*(SELECT COUNT(des) FROM log WHERE dater BETWEEN '$startdate' AND '$enddate')) as tot FROM Log WHERE dater BETWEEN '$startdate' AND '$enddate' GROUP BY des ORDER BY tot DESC

 

please help

[mac_gyver]

rather than showing what you tried, show some sample data, for at least two different des group values, and what result you want from that data.

[bensonsearch]

Good point, data like below

 

des              |       Price         |           qty      |      dater

mango                 5.00                       1                2013-11-17

mango                10.00                      2                2013-11-17

mango                5.00                        1                2013-11-17

banana               2.00                       10               2013-11-17

 

what im hoping to output is the combined number of counting the des and the qty together (so I can rank highest selling to lowest)

 

des            |         Price         |          total sold     

banana                2.00                     10

mango                 20.00                    4

 

and i just solved it myself by looking at this data OMG so dumb

 

what I was wanting is the below, why do i try and make it harder on myself:

 

SELECT des,COUNT(des),SUM(price),SUM(qty)  FROM Log WHERE dater BETWEEN '$startdate' AND '$enddate' GROUP BY des ORDER BY SUM(qty) DESC