CODEWITHSUNDEEP
phpmysqlsql
Dizzy49
Dizzy49

Reputation: 1520

How to total results within the query

I have the following query:

SELECT cb.customers_id, cb.products_id, p.products_model, pd.products_name, cb.customers_basket_quantity, p.products_price, (p.products_price * cb.customers_basket_quantity) AS product_total
    FROM customers_basket cb, products p, products_description pd
    WHERE cb.customers_id =194075
        AND cb.products_id = pd.products_id
        AND p.products_id = pd.products_id

I am trying to figure out if I can get a "cart_total" without having to do another query, or use PHP to keep a running total.

I wasn't sure if I could use the CASE statement to do something.

Suggestions?

Upvotes: 0

Views: 59

Answers (3)

cenk
cenk

Reputation: 1429

Try this, you will get the resulting sum at the last row of the query.

All other columns will be null.

SELECT  cb.customers_id, 
        cb.products_id, 
        cb.customers_basket_quantity, 
        p.products_model, 
        p.products_price, 
        pd.products_name,
        (p.products_price * cb.customers_basket_quantity) AS product_total,
        null as cart_total
    FROM products p 
        INNER JOIN customers_basket cb ON p.products_id = cb.products_id
        INNER JOIN products_description pd ON pd.products_id = p.products_id
    WHERE cb.customers_id = 194075

union all

SELECT null, null, null, null, null, null, null,
        SUM(p.products_price * cb.customers_basket_quantity) AS cart_total
    FROM products p 
        INNER JOIN customers_basket cb ON p.products_id = cb.products_id
    WHERE cb.customers_id = 194075

sample data

Upvotes: 0

Nir Alfasi
Nir Alfasi

Reputation: 53525

SELECT cb.customers_id, cb.products_id, p.products_model, pd.products_name, cb.customers_basket_quantity, p.products_price, (p.products_price * cb.customers_basket_quantity) AS product_total,

(SELECT sum(p.products_price * cb.customers_basket_quantity) 
FROM customers_basket cb, products p
WHERE cb.customers_id =194075
AND cb.products_id = p.products_id
group by cb.customers_id) AS cart_total

FROM customers_basket cb, products p, products_description pd
WHERE cb.customers_id =194075
AND cb.products_id = pd.products_id
AND p.products_id = pd.products_id

A link to Fiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can do a cart total, but you want to drop some of the columns from the group by and select (they are not appropriate at the cart level). You should also use standard ANSI join syntax:

SELECT cb.customers_id, cb.customers_basket_quantity,
       sum(p.products_price * cb.customers_basket_quantity) AS product_total
FROM customers_basket cb join
     products p
     on cb.products_id = p.products_id join
     products_description pd
     on p.products_id = pd.products_id
WHERE cb.customers_id = 194075
GROUP BY cb.customer_basket_id;

Information at the product level doesn't make sense at the basket level. You could create lists of product names, using, say:

group_concat(p.products_name) as Products_Name

but individual values don't make sense.

Upvotes: 0

Related Questions