Member Avatar for nkarvi

Hi everyone!

I have the following problem I am trying to solve:

I have one table with data and need to access the same column twice, everyone with different criteria and then output the results on one table.

I tried

SELECT e.user, SUM(e.column), SUM(f.column)
FROM table AS e INNER JOIN table AS f
ON e.user = f.user
WHERE  (e.criterion1 and f.criterion2)
GROUP BY e.user

sums are calculated multiple times. What is wrong?


Thank you very much in advance.

  • 2 Contributors
  • 2 Replies
  • 113 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by nkarvi
Member Avatar for sknake

Please use code tags when posting queries on daniweb:

[code=sql] ...query here...

[/code]

Next --- I cannot tell what you are trying to do since you have abstracted away the table and column names. You can access the same column twice for multiple calculations so you do not need to join the table to itself unless you're trying to do certain things. Can you please post your table, its' structure, some sample rows, and indicate what your desired output is?

Member Avatar for nkarvi

Thank you very much for your help.

I googled a bit more on "accessing the same table multiple times" as you wrote and ende up using a case statement in the sum like this:

select sum(case when type = 1 then quantity else 0 end)

where the type is the criterion and the quantity is the ammount I want to add.

It worked ok.

Thanks very much again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.