CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Shuckyducky
Shuckyducky

Reputation: 71

Wrong output in mySQL query

I'm relatively new to mySQL and phpMyAdmin and I am having problems with a query I am trying to put in. I want the "Units Sold" to be a number, but the whole column prints out "Units Sold" for some reason. I already tried removing both "Units Sold" and i've tried removing just one, but I am not sure what is going wrong here. Sorry if I am not clear enough, but here is my query:

SELECT brand_name, brand_type, Round(avgprice,2) AS "Average Price", "Units Sold"
FROM lgbrand b JOIN (SELECT brand_id, Avg(prod_price) AS avgprice 
            FROM lgproduct 
            GROUP BY brand_id) sub1 
ON b.brand_id = sub1.brand_id
    JOIN (SELECT brand_id, Sum(line_qty) AS "Units Sold" 
    FROM lgproduct p JOIN lgline l ON p.prod_sku = l.prod_sku 
GROUP BY brand_id) sub2
    ON b.brand_id = sub2.brand_id
ORDER BY brand_name;

And here is a picture to go along with what the output looks like:

image here

Upvotes: 1

Views: 114

Answers (3)

Charif DZ
Charif DZ

Reputation: 14741

if you want to keep the space in the name of you column use :

  SELECT brand_name, brand_type, Round(avgprice,2) AS "Average Price", `Units Sold`
FROM lgbrand b JOIN (SELECT brand_id, Avg(prod_price) AS avgprice 
            FROM lgproduct 
            GROUP BY brand_id) sub1 
ON b.brand_id = sub1.brand_id
    JOIN (SELECT brand_id, Sum(line_qty) AS "Units Sold" 
    FROM lgproduct p JOIN lgline l ON p.prod_sku = l.prod_sku 
GROUP BY brand_id) sub2
    ON b.brand_id = sub2.brand_id
ORDER BY brand_name;

in postgres and Oracle you can do "fieldname" by in Mysql use

Upvotes: 0

Hackerman
Hackerman

Reputation: 12295

You are very close:

SELECT 
   brand_name, brand_type, 
   round(avgprice,2) AS "Average Price", sub2.Units_Sold AS "Units Sold" 
FROM lgbrand b JOIN (SELECT brand_id, Avg(prod_price) AS avgprice 
        FROM lgproduct 
        GROUP BY brand_id) sub1 
ON b.brand_id = sub1.brand_id
JOIN (SELECT brand_id, Sum(line_qty) AS Units_Sold 
     FROM lgproduct p JOIN lgline l ON p.prod_sku = l.prod_sku 
GROUP BY brand_id) sub2
ON b.brand_id = sub2.brand_id
ORDER BY brand_name;

Upvotes: 2

Charles PM
Charles PM

Reputation: 11

In mySQL you are supposed to use the backticks ` for columns and table names. Here mySQL considers "Units Sold" as a variable from your side, which is why it is printed like this in the result.

SELECT b.`brand_name`, b.`brand_type`, Round(sub1.`avgprice`, 2) AS "Average Price", sub2.`Units Sold`
FROM lgbrand b JOIN (SELECT `brand_id`, Avg(`prod_price`) AS avgprice 
            FROM lgproduct 
            GROUP BY `brand_id`) sub1 
ON b.`brand_id` = sub1.`brand_id`
    JOIN (SELECT `brand_id`, Sum(`line_qty`) AS "Units Sold" 
    FROM lgproduct p JOIN lgline l ON p.`prod_sku` = l.`prod_sku` 
GROUP BY `brand_id`) sub2
    ON b.`brand_id` = sub2.`brand_id`
ORDER BY `brand_name`;

Upvotes: 1

Related Questions